How to automatically identify cell-based array delimiter?

[Rnkhch]

Hello,

Suppose there are cells that store a few numbers/texts as below:

Book1
	A	B	C
1
2		82,78,53,A-,37,B
3		C+;67;45;12;A;92
4		67,13;33,F,83
5
Sheet2

How can I automatically figure out what the delimiter is for each cell? And if there is more than one delimiter, get an error message?

Thanks for any input!

P.S. I need this to simplify a cool LAMBDA that I'm working on which I will post later in LAMBDA Functions

 

[jasonb75]

Do you just want to identify the delimiter or do you need to convert it it to an actual array?

If you have the TEXTSPLIT function then that is likely to be the easiest way to do either (I haven't used it yet so can't say for certain).

Please note that the syntax of the concept formulas below may not be accurate, I'm doing this on phone so brackets and commas often get missed.

Without it you could use LEN(text)-LEN(SUBSTITUTE(text,delimiter,"")) to test for a shorter string when the delimiter is removed. Splitting it to an array would mean identifying the delimiter then using something like MID(SUBSTITUTE(text,delimiter,REPT(" ",LEN(text))text,FIND(delimiter,text))*SEQUENCE(LEN(text)-LEN(SUBSTITUTE(text,delimiter,"")),,0),LEN(text))

Whatever you go with, you would need to give the formula a list of possible delimiters to work with, I don't know of any current function that has the ability to guess what it might be.

 

[Peter_SSs]

Suppose there are cells that store a few numbers/text as below

I'm not sure that I would have a suggestion for you, but there would need to be some logic to determine what constitutes a delimiter.
For example if the cells store numbers/text then without such logic, cell B2 might store these texts
"82,78,53,A" and ",37,B" with a delimiter of "-"

 

[Rnkhch]

Thanks guys!

@jasonb75
1.

Do you just want to identify the delimiter?

My initial goal was to write a LAMBDA that would easily call the nth element of such array. I wrote a LAMBDA based on one of my older threads:

Is it possible to store more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

Hello, I'm wondering if it is possible to store more than one number in an array. I'm hoping I can reduce the size of my large score sheets by storing the scores of each student in one cell where for example the first element would be math score, the second physics score, the third chemistry...

www.mrexcel.com

=LAMBDA(element_number,cell_array,delimeter)

The LAMBDA works well, but I wanted to see if I could simplify it by making it automatically find the delimiter, so that the LAMBDA would look like this:

=LAMBDA(element_number,cell_array)

But I realized that, as Peter mentioned too above, it will be really hard to determine what the delimiter would be. So I guess I'm gonna have to leave the "delimiter" parameter in my LAMBDA, unless you guys can come up with any magic solution(s) .

2.

or do you need to convert it to an actual array?

Actually my next goal was to write another LAMBDA that would break the cell-based array into a spilled array. So since you already have a function, I'm gonna use that and test it, but the syntax is not right and I couldn't fix it (I tried a number of ways).

3.
Do you mind checking the syntax of your formula and let me know the corrected syntax:
=MID(SUBSTITUTE(text,delimiter,REPT(" ",LEN(text))text,FIND(delimiter,text))*SEQUENCE(LEN(text)-LEN(SUBSTITUTE(text,delimiter,"")),,0),LEN(text))


@Peter_SSs
Totally agree with your point. I have been thinking the same. One idea I came up with was to have a function that would use the most reasonable delimiters such as comma and semicolon and give a message if it didn't find them, but it's probably gonna be very complex even for you guys to code that. So it's probably best to leave the delimiter parameter in the LAMBDA, but please let me know if you have any additional thoughts.

 

[Peter_SSs]

One idea I came up with was to have a function that would use the most reasonable delimiters such as comma and semicolon ..

Well, if there was only a small number of them, could you use something like one of these?

Rnkhch.xlsm
	B	C	D	E
1		Count of each delimiter	No. of different delimiters	No. of different delimiters
2	82,78,53,A-,37,B	, 5 ; 0 - 1	2	2
3	C+;67;45;12;A;92	, 0 ; 5 - 0	1	2
4	67,13;33,F,83	, 3 ; 1 - 0	2	2
5	68/55/704/8	, 0 ; 0 - 0	0	1
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=LET(L,LEN(B2),TEXTJOIN(CHAR(10),,", "&L-LEN(SUBSTITUTE(B2,",","")),"; "&L-LEN(SUBSTITUTE(B2,";","")),"- "&L-LEN(SUBSTITUTE(B2,"-",""))))
D2:D5	D2	=COUNT(FIND({",",";","-"},B2))
E2:E5	E2	=LET(delims,",;-+/",COUNT(FIND(MID(delims,SEQUENCE(LEN(delims)),1),B2)))

 

[Rnkhch]

You are an absolute genius, not relative!! So I could use your column E formulas and say for example if the numbers of delimiters is 1 (such as in E5), then proceed with the rest of the operation:

=LET(t,LET(delims,",;-+/",COUNT(FIND(MID(delims,SEQUENCE(LEN(delims)),1),B5))),IF(t=1,operation,"fix delimiters"))

Now, I would need one more function to output the "only" delimiter for when there is only one delimiter, so that I can supply that delimiter to my main function's "delimiter" parameter. Thanks much!


P.S.
I'm taking back my previous statement

it's probably gonna be very complex even for you guys to code that

 

[Peter_SSs]

ow, I would need one more function to output the "only" delimiter for when there is only one delimiter

Something like this?

Rnkhch.xlsm
	B							I
1
2	82,78,53,A,37,B							,
3	C+;67;45;12;A;92							fix delims
4	67,13;33,F,83							fix delims
5	68/55/704/8							/
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=LET(delims,",;-+/",L,LEN(delims),fnd,FIND(MID(delims,SEQUENCE(L),1),B2),IF(COUNT(fnd)=1,MID(delims,MATCH(L,fnd),1),"fix delims"))

 

[Rnkhch]

Fabulous! That's exactly what I needed! Thanks!

 

[Rnkhch]

One question: in your latest formula of post #7, how does the MATCH function work? From what I see in the formula evaluate window:

MATCH(5,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;3})

evaluates to 5, but there are no 5s in the lookup_array Is it due to the match_type not being available, so it's doing an approximation?

 

[Peter_SSs]

how does the MATCH function work?

Actually, my formula needs amendment but I can't do it right now. I'll be back in a while.