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

 

[Rnkhch]

Sure, thank you! Please take your time. TTYS

 

[Sulprobil]

Hello,

My suggestion would be to TEXTJOIN the output of

Split On Delimiters

www.cpearson.com

with the delimiter of your choice.

Regards,
Bernd

 

[Peter_SSs]

New suggestion with example of failure of the previous formula.

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

Cell Formulas
Range	Formula
I2:I6	I2	=LET(delims,",;-+/",fnd,FIND(MID(delims,SEQUENCE(LEN(delims)),1),B2),IF(COUNT(fnd)=1,MID(delims,MATCH(9^9,fnd),1),"fix delims"))
J2:J6	J2	=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"))

how does the MATCH function work?

For cell I6 above the equivalent formula evaluation part is
MATCH(387420489,{#VALUE!;8;#VALUE!;#VALUE!;#VALUE})
The first #VALUE is because the first delimiter character (comma) is not found.
The 8 is where the second delimiter (semicolon) is first found. The actual number (8) is irrelevant but it indicates the semicolon was found
The other 3 #VALUE! items are because the 3rd, 4th & 5th delimiters (-+/) were not found.

This MATCH part of the formula only gets evaluated if there is a single delimiter so there will always be exactly 1 number and the rest #VALUE! errors. When MATCH cannot find the big number (9^9) it is looking for, it matches the one (smaller) number that it can find. That match is in the 2nd position of that array so it means that the delimiter in question is the second character in the 'delims' string

 

[Rnkhch]

Awesome! Thanks much for the function and the explanation I'm gonna incorporate this in my LAMBDA and see how it goes. I'll update.

 

[Peter_SSs]

Cheers. Good luck!