Convert the Sub below into a built-in Function-CodePudding

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

CodePudding user response：

If you are using a verion that supports

CodePudding user response：

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.