Converting an array to single digits

[JoePage]

Hello!

Ok, so I don't use excel a great deal but I do have some basic ideas.

I'm creating a worksheet that converts an array into single digits, those digits are then converted to binary and placed into rows on a table. I then turn the columns into a hex number. I have got all of this worked out except for the very first part.

Here are three examples of my input arrays, the elements to the left are left out.

[0, 27, 9, 18]
[]
[0, 10, 10, 31, 10, 31, 10, 10]

I want to convert the above into 8 separate numbers into 8 separate cells, so for example;

0 0 0 0 0 27 9 18


I would greatly appreciate the help,
Thank you !

[XOR LX]

Hi,

So your input is a string contained within a single cell, e.g. "[0, 27, 9, 18]", i.e. including the square brackets?

Regards

[JoePage]

Hi, yes it is but I can omit the square brackets.

[XOR LX]

Ok, thanks. Then without the square brackets would be:

=IF($A1="","",INDEX(FILTERXML("<a><b>"&SUBSTITUTE(REPT("0,",7-LEN($A1)+LEN(SUBSTITUTE($A1,",","")))&$A1,",","</b><b>")&"</b></a>","//b"),COLUMNS($A1:A1)))

and copied right.

Note that FILTERXML only works in the desktop version of Excel, not online.

Regards

[JoePage]

Wow!

That's amazing, thank you,
I made a mistake in my original statement,
[0, 27, 9, 18]
I would want the output to be 0 27 9 18 0 0 0 0

I'd give it a shot myself but your formula is way over my current understanding of excel,

I truly greatly appreciate your help!

[XOR LX]

Even easier!

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE($A1,",","</b><b>")&"</b></a>","//b[position()="&COLUMNS($A1:A1)&"]"),"")

Regards

[JoePage]

excellent !
Thank you very much, this is exactly what I need