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

I edited the question to format the code you provided, which is a little confusing. Why don't you edit the question to include a description of what you are trying to accomplish and anything you have tried so far. — Chris Strickland, Nov 28 '21 at 22:10
I am trying to build a function to parse comma delimited text in a cell into rows. The Sub code works fine but requires a manual "run"; I want it to be a function. Thanks — user17534812, Nov 28 '21 at 22:31
Still confusing: Do you mean (e.g.) assign it to a short-cut (or other control), so it can be `Run` by the user? Or do you mean using it as `User-Defined Function` (`UDF`)? If the latter: While you can write to other cells from a `UDF` it's somewhat of a convoluted process (and not recommended by many). — Spinner, Nov 28 '21 at 22:40
A function cannot affect any cells you want, so you have to send in a range (like at SUM() ) and allocate the function's area to respond to as ARRAY. But then again you can do the same with Transpose() function without VBA. Also from Functions i think that one cannot ask for input, you have to send what you want via the caller... my advice, stick to native Transpose() and look in tutorials for ARRAYS in Excel — Apostolos55, Nov 28 '21 at 22:42
Transpose will not work for it takes cells into cells. 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 =my UDF(A1) to list the M...s in say cells B1 through B3 — user17534812, Nov 28 '21 at 22:55
And in the question, add all the requirements, like user input, etc, some sample data, and what output you expect to get. — Chris Strickland, Nov 28 '21 at 22:58
Thanks, you can actually do this with Excel's built in functions, although it's kind of roundabout. I'll see if I can put something together, but I think that answer below should work. What version is your excel? — Chris Strickland, Nov 29 '21 at 02:23

chris neilsen · Answer 1 · 2021-11-29T00:11:42.490

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)

edited Nov 29 '21 at 00:11

answered Nov 29 '21 at 00:06

chris neilsen

52,446
10
84
123

Instructive hint to the string length limit of 32765 characters :+) ... Side note: It might be of some interest, however to add another limitation (being *superimposed*, however in this post by the narrower characters limit of 32765): a transposition applied on *arrays* of more than 2 ^ 16, i.e. `65536` rows will truncate results (even without error/2016+). (Applied on range rows, one get correct results using the worksheet function: `Application.WorksheetFunction.Transpose`) – T.M. Nov 29 '21 at 18:23

Chris Strickland · Answer 2 · 2021-11-29T17:42:34.620

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.

`FilterXML()` was introduced in vers. 2013, after all, eight years ago. — T.M., Nov 29 '21 at 17:26

Convert the Sub below into a built-in Function

2 Answers2