Parse a cell and return associated values into another cell

Question

In Excel 2016, I am trying to write either an array formula or a vba function to complete the following:

1.) User enters a string (a Project Name) into a cell (Cell D1 in attached example)

2.) Cells in Column B will fill in with the number(s) associated with the Project Name string based on the contents of Column A each separated by commas

Column A contains some number of comma delimited entries. Each entry contains a Project Name and an associated Number separated by a % character

For example, the first row in the example image contains three entries:

Project1 1234
Project2 2345
Project3 5678

This is entered as "Project1%1234,Project2%2345,Project3%5678" in the cell

I have attached an example image that contains a blank version of my problem and below that is what the desired result would be if the user entered Project1. I hope this illustrates the problem well but if not I can clarify.

Is this possible to accomplish with an array formula or will this require a user defined function in vba?

Answer 1

Yes, this is possible with just an array formula. It's not exactly simple, though.

Array enter (Ctrl+Shift+Enter) the following formula in B2 and copy-paste/fill-down the column:

{=TEXTJOIN(",",TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),IFERROR(FIND(",",TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))))-1,0)))}

---

The prettified formula is as follows:

{=
TEXTJOIN(
  ",",
  TRUE,
  LEFT(
    TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),
    IFERROR(
      FIND(
        ",",
        TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2)))
      )-1,
      0
    )
  )
)}

Explanation:

The formula works by replacing the project name plus separator in the target string with a long sequence of spaces, and then "splitting" the string up into an array of chunks, each of which begins with the project number of a matched project. That is the output of the TRIM(…) functions.

Then the leftmost part of each chunk up to, but not including, the first comma, i.e. the project number, is extracted.

Finally the project numbers are concatenated with the TEXTJOIN() function.

The IFERROR part of IFERROR(FIND(",",TRIM(…))-1,0) is required to remove the errors returned when the FIND() function fails, with the resulting null strings being ignored by TEXTJOIN().

Notes:

• The prettified formula actually works if entered.

Answer 2

Since I didn't see robinCTS's answer in time I ended up writing a vba function that solves this problem. I figured I would share it even though it's a bit clunky just in case it helps someone with the same problem.

Function ParseK(celltxt As String, userin As String) As String
Dim project_name As String
Dim number As String
Dim final_result As String
Dim string_array() As String
ReDim string_array(5)
Dim i As Variant
string_array = Split(celltxt, ",")
For Each i In string_array
    number = Right(i, Len(i) - InStr(i, "%"))
    project_name = Left(i, InStr(i, "%") - 1)
    If InStr(project_name, userin) > 0 Or project_name = userin Then final_result = final_result & number & ","

Next i
ParseK = final_result
End Function