Formula to split cell into array

[dave2018]

for some reason i can't wrap my head around this, but it's so simple!

my string: "4,18,3,23,56,127,1,89"

i need it to be split (with =MID) into {4,18,3,23,56,127,1,89} so i can do different calculations on it etc.

thx.

 

[jtakw]

Hi,

I'm not understanding your question.

Do you mean to split the string into Individual cells, like, if A1 is "4,18,3,23,56,127,1,89", then B1 would be 4, C1 would be 18, E1 would be 3, etc...?

 

[dave2018]

Hi,

I'm not understanding your question.

Do you mean to split the string into Individual cells, like, if A1 is "4,18,3,23,56,127,1,89", then B1 would be 4, C1 would be 18, E1 would be 3, etc...?

Hi jtakw,

Noooo... . I want it in 1 cell, so if you would highlight the formula in edit mode u would get this:

<a href="https://imgbb.com/"><img src="https://i.ibb.co/t35bkZS/Capture.png" alt="Capture" border="0"></a>

thx

 

[dave2018]

So i found this GENIUS formula:

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

Which I totally dont understand.. (i mean i see the logic, add certain code tags instead of the delimiter and then FILTERXML [wtvr that means] / Parse it by the //s tags.

but is this the accepted way to do it? or is there a more "normal" way to do it?

in general I like formulas that 'make sense' to me (and that i can technically come up with, unlike the FILTERXML - who uses that?!), even though the other one might work, if u know what i mean...

Thank!

 

[Rick Rothstein]

I think FILTERXML is a UDF (user defined function) which is function constructed in Visual Basic for Applications that can be used on a worksheet as if it were a built-in Excel function. Here is a UDF that will handle your type of values...

Function MakeArray(S As String) As Variant
  MakeArray = Evaluate("{" & S & "}")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use MakeArray just like it was a built-in Excel function. For example, if A1 contains this (note, no quote marks)...

4,18,3,23,56,127,1,89

then if you put this formula in another cell...

=MakeArray(A1)

and select the MakeArray(A1) part of the formula in the Formula Bar, then it will show the array you asked for.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[MARK858]

Hi Rick, FILTERXML isn't a UDF it is a native function in later versions...

https://support.office.com/en-us/article/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7

 

[jtakw]

I have Excel 2016 ( NON 365 ) and I have the FILTERXML as a native formula function... have Never used it thou...

 

[dave2018]

I think FILTERXML is a UDF (user defined function) which is function constructed in Visual Basic for Applications that can be used on a worksheet as if it were a built-in Excel function. Here is a UDF that will handle your type of values...

Function MakeArray(S As String) As Variant
  MakeArray = Evaluate("{" & S & "}")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use MakeArray just like it was a built-in Excel function. For example, if A1 contains this (note, no quote marks)...

4,18,3,23,56,127,1,89

then if you put this formula in another cell...

=MakeArray(A1)

and select the MakeArray(A1) part of the formula in the Formula Bar, then it will show the array you asked for.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you @Rick Rothstein, but i can't stand UDF's because i like using the generic formula's at hand, plus you save as a regular workbook, AND it's interchangeable with google sheets (which cant accept udf's).

i'm looking for the common way to parse the numbers in a string with an array formula in 1 cell.
my guess would be using MID and SUBSTITUTE etc., but i cant wrap my head around it.

again, the FILTERXML was a beautiful way of doing it, but i'm sure there is a more "logical" way to build it.

ps. dont go crazy over this question, because it's solved (with the FILTERXML function). I just want a better formula that people looking at it will understand etc..

Thanks again!

 

[dave2018]

and at last, i think I found what i was looking for..

Excel 2016 (Windows) 32 bit
	A	B
1	4,18,3,23,56,127,1,89	4
Sheet2

Cell Formulas
Range	Formula
B1	{=MID(SUBSTITUTE(A$1,",",REPT(" ",99)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*99-98,99)*1}
Press CTRL+SHIFT+ENTER to enter array formulas.

and when evaluating [F9] in edit mode, i succesfully get:


but it has indirect . but i expected to need that to know how many values i need to separate (by counting how many delimiters...)

PS. i dont have insider version of excel, but i hear that the =SEQUENCE function takes care of all the incrementer arrays... namely, ROW(INDIRECT("A1:A"&"Custom Number")) etc.

PPS. This should probably go in another thread, but can anyone make a UDF that mimics the SEQUENCE function, for those who dont have it yet. (i'm going against my usual dislike for UDF's, because this one is soooo useful in everyday things...! come to think of it, it should have been created a long time ago...)

 

[dave2018]

PPS. This should probably go in another thread, but can anyone make a UDF that mimics the SEQUENCE function, for those who dont have it yet. (i'm going against my usual dislike for UDF's, because this one is soooo useful in everyday things...! come to think of it, it should have been created a long time ago...)

I posted this question in a new thread:
https://www.mrexcel.com/forum/excel-questions/1081780-udf-sequence-function.html#post5197646

please answer it there. thx