Convert the Sub below into a built-in Function

Question

I am VERY new to VBA.

I am trying to build a UDF function to parse comma delimited text in a cell into rows. I have a Sub that works fine, but requires a manual "run"; I want it to be a function.

Say I have in cell A1 the following string comma delimited string

M89-76,M64-62,M76-80

and I want to list each M... in a separate row cell. The sub code accomplishes this but requires a manual run; I need a UDF of the sub code so I can type =myUDF(A1) into B1 and the list of M...'s is returned in cells B1 through B3

Sub TransposeRange()

  Dim rng As Range
  Dim InputRng As Range, OutRng As Range

  Set InputRng = Application.Selection.Range("A1")
  Set InputRng = Application.InputBox("Range(single cell) :", xTitleId, InputRng.Address, Type:=8)
  Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
  
  Arr = VBA.Split(InputRng.Range("A1").Value, ",")
  OutRng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = 
  Application.Transpose(Arr)

End Sub

Answer 1

If you are using a verion that supports Dynamic Arrays (Office365) then your UDF can spill the result into as many cells as required.

Function MyUDF(r As Range) As Variant
    Dim s() As String
    s = Split(r.Value2, ",")
    MyUDF = Application.Transpose(s)
End Function

For data in A put =MyUDF(A1) in Cell B1, the result will spill down

Note that Application.Transpose has a string length limit of 32765 characters. If the data in A1 is longer that that, the result will be truncated. In that case you would need to code the transpose (loop s, into a 2D array)

If you don't have Dynamic Arrays, the formula will still work when entered as an Array Formula into a range large enought to hold the result (eg B1:B3)

Answer 2

If you have FILTERXML (introduced in 2013, thanks T.M.), then you can do this with a formula:

=FILTERXML("<t><x>" & SUBSTITUTE(A1, ",", "</x><x>") & "</x></t>", "//x")

Where you convert the string into an xml formatted tree and then pass in an xpath selector. This will spill into as many rows as are needed.

You can also use this. It's made much simpler by LET, but I'm going to give the non-parameterized version, because if you have LET you should have FILTERXML anyway:

=TRIM(
  MID(
    SUBSTITUTE(A1, ",", REPT(" ", 100)), 
    IF(
      ROW(OFFSET(A1, 0, 0, LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)) = 1,
      1, 
      (ROW(OFFSET(A1, 0, 0, LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*100
    ), 
    100
  )
)

where you replace the commas with an arbitrarily long amount of whitespace, then use MID to select out substrings, and ROW to generate an array of indexes the size of the elements in the string. Wrap it in TRIM to get rid of the whitespace.

If you need to do this with a UDF, then you can do this:

Function splitIt(raw)
    splitIt = Application.Transpose(Split(raw, ","))
End Function

and use it like any other function. This is functionally identical to Chris Nielsen's solution.

---

Will update shortly with two other options for other Excel versions.