=TEXTSPLIT() of array not returning 2d array

Question

I'm expecting (desiring?) the formula:

=TEXTSPLIT({"a.b";"c.d"},".")

to return four cells: a and b in the first row, c and d in the second row. It doesn't. It returns a in the first row and c in the second row and no second column with b and d.

Is there a way around this?

(The original problem is using a lambda that converts 1.2.3.15-style decimal IP addresses to hex 01.02.03.0F and calling that lambda with a dynamic array of the decimal addresses. This is that lambda:

=LAMBDA(IPdecimal,TEXTJOIN(".",,DEC2HEX(TEXTSPLIT(IPdecimal,"."),2)))

This lambda works fine for a one element IPdecimal parameter. It fails for the array of IPdecimal values.

Before the TEXTJOIN lambda, I was using this ugly hack:

=LAMBDA(ipdecimal,DEC2HEX(IF(ISERR(LEFT(ipdecimal,FIND(CHAR(46),ipdecimal)-1)),ipdecimal,LEFT(ipdecimal,FIND(CHAR(46),ipdecimal)-1)), 2)&"."&DEC2HEX(MID(MID(MID(SUBSTITUTE(ipdecimal,CHAR(46),"^",1),1,256),FIND("^",SUBSTITUTE(ipdecimal,CHAR(46),"^",1)),256),2,FIND(CHAR(46),MID(MID(SUBSTITUTE(ipdecimal,CHAR(46),"^",1),1,256),FIND("^",SUBSTITUTE(ipdecimal,CHAR(46),"^",1)),256))-2), 2)&"."&DEC2HEX(MID(MID(MID(SUBSTITUTE(ipdecimal,CHAR(46),"^",2),1,256),FIND("^",SUBSTITUTE(ipdecimal,CHAR(46),"^",2)),256),2,FIND(CHAR(46),MID(MID(SUBSTITUTE(ipdecimal,CHAR(46),"^",2),1,256),FIND("^",SUBSTITUTE(ipdecimal,CHAR(46),"^",2)),256))-2), 2)&"."&DEC2HEX(IF(LEN(ipdecimal)-LEN(SUBSTITUTE(ipdecimal,CHAR(46),""))=0,ipdecimal,RIGHT(ipdecimal,LEN(ipdecimal)-FIND("",SUBSTITUTE(ipdecimal,CHAR(46),"",LEN(ipdecimal)-LEN(SUBSTITUTE(ipdecimal,CHAR(46),"")))))), 2))

You can see why I want to use the TEXTJOIN(".",TEXTSPLIT()) version.)

Answer 1

Hi @Dick Watson

First, we can kown the formula ={"a.b";"c.d"} in one Excel cell will return 1 two-dimensional array from plane data, but the array formula {={"a.b";"c.d"}} in one Excel cell will just show the first dimension data of the array. It is by design.

To let the array {"a.b";"c.d"} show 'a' in the first row, first column; show 'b' in the first row, second column; show 'c' in the second row, first column and show 'd' in the second row, second column, I suggest you add 'TEXTJOIN' function to convert this array to a string in one cell.

Then perform both row splitting and column splitting for this string. Combining this step and the step above, the formula can be =TEXTSPLIT(TEXTJOIN(";",TRUE,{"a.b";"c.d"}),".",";").

If you want to convert 1.2.3.15-style decimal IP addresses to hex 01.02.03.0F, the formula can be =TEXTJOIN(".",,DEC2HEX(TEXTSPLIT(TEXTJOIN(";",TRUE,{"1.2";"3.15"}),".",";"),2)).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Thanks for the post. The problem is not getting it to work for one row at a time, it's getting it to work for a dynamic array input and return multiple rows of output.

From some helpful posters in a thread at answers.microsoft.com, I solved my case using MAP(), LAMBDA(), TEXTJOIN(), DEC2HEX(), and TEXTSPLIT():

I am a little mystified why the simplified case to just demonstrate the TEXTSPLIT() fails while the full-up …TEXTJOIN(…DEC2HEX(TEXTSPLIT(… case works. Go figure.

Answer 3

Thanks for looking at this. Since the problem I was out to solve was to take a dynamic array (multi-row, one column) of dotted decimal IPv4 addresses and turn it into a dynamic array (multi-row, one column) of dotted hex IPv4 addresses, only one of these solutions gets me there. Returning them combined in one cell or returning just the first part of the address or addresses or just failing CALC! is not useful. So, lots of interesting quirks are observed here, but I ultimately found a solution. (Given all the quirky results, it's a miracle the solution works, but, hey, never turn down a win.)