Text To Columns with odd items in brackets

[mad3]

Hi,

I have two columns with 9 or more single digits numbers that are text. The issue with simple text to columns is that if any of the 9 numbers is a double digit number it looks like this in the cell...

Q​	R​
0001010(11)0	400100000
0000001000	000000200
1010101000	00000(10)000

I want to extract each individual number in column Q to separate columns; same with column R. to a separate column to study the totals, but have an issue every time it runs into the odd ball double digit number which is always like this (10)

If it helps, these are line scores from baseball games, but have a number of them with double digit scoring in a single inning.
any ideas on how to extract each number including the double digit numbers?

Thanks, Mike in Cali...

 

[JEC]

Nice one Felix! Here a technique (rebuild your formula) where you don't need the name manager for recursive Lambda's.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0001010(11)0	0	0	0	1	0	1	0	11	0
2	0000001000	0	0	0	0	0	0	1	0	0	0
3	1010101000	1	0	1	0	1	0	1	0	0	0
4	400100000	4	0	0	1	0	0	0	0	0
5	000000200	0	0	0	0	0	0	2	0	0
6	00000(10)000	0	0	0	0	0	10	0	0	0
7	0123456789	0	1	2	3	4	5	6	7	8	9
8	(10)(11)(12)(13)(14)(15)(16)(17)(18)(19)	10	11	12	13	14	15	16	17	18	19
9	(20)(21)(22)(23)(24)(25)(26)(27)(28)(29)	20	21	22	23	24	25	26	27	28	29
10	(30)(31)(32)(33)(34)(35)(36)(37)(38)(39)	30	31	32	33	34	35	36	37	38	39
11	(40)(41)(42)(43)(44)(45)(46)(47)(48)(49)	40	41	42	43	44	45	46	47	48	49
12	(50)(51)(52)(53)(54)(55)(56)(57)(58)(59)	50	51	52	53	54	55	56	57	58	59
13	(60)(61)(62)(63)(64)(65)(66)(67)(68)(69)	60	61	62	63	64	65	66	67	68	69
14	(70)(71)(72)(73)(74)(75)(76)(77)(78)(79)	70	71	72	73	74	75	76	77	78	79
15	(80)(81)(82)(83)(84)(85)(86)(87)(88)(89)	80	81	82	83	84	85	86	87	88	89
16	(90)(91)(92)(93)(94)(95)(96)(97)(98)(99)	90	91	92	93	94	95	96	97	98	99
Sheet1

Cell Formulas
Range	Formula
B1:J1,B7:K16,B4:J6,B2:K3	B1	=LET(R,LAMBDA(R,str,i,IF(i<100,R(R,SUBSTITUTE(str,"("&i&")",CHAR(48+i)),i+1),CODE(MID(str,SEQUENCE(,LEN(str)),1))-48)),R(R,A1,10))
Dynamic array formulas.

 

[felixstraube]

Thanks! And thanks for sharing the technique!!! Nice one!

 

[mad3]

Yes, you are right. I wanted to extract q and place to the right. What I was amiss about was how many columns I had vacated for the formula to work. My bad. I will follow your suggestion and change sequence to number of vacated columns. Thank you!

Hi Rick,

While formula works for home team,
I adjusted the sequence to 22 ( number of vacant columns to right of (visiting team) column formula is checking and it still didn't work.

 

[Rick Rothstein]

Hi Rick,

While formula works for home team,
I adjusted the sequence to 22 ( number of vacant columns to right of (visiting team) column formula is checking and it still didn't work.

Can you give me some specific examples that did not work and tell me exactly what did not work so I can test the formula against actual data and then know where to look for the problem.

 

[mad3]

Can you give me some specific examples that did not work and tell me exactly what did not work so I can test the formula against actual data and then know where to look for the problem.

Here is a snapshot of a few selected rows from one of sheets. The H-LINESCORE works fine. No other data to right of column where formula is.
The V-LINESCORE would not work when placing the formula in cell U4 or any of the cells below that one. This same scenario happens on all spreadsheets for V-LINESCORE.

V-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	H-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13
100000000																															00121040x	0	0	1	2	1	0	4	0	x
00010000000																															00000(10)00001	0	0	0	0	0	10	0	0	0	0	1
010203100																															000011020	0	0	0	0	1	1	0	2	0
000011001																															00010510x	0	0	0	1	0	5	1	0	x
001300000																															00011021x	0	0	0	1	1	0	2	1	x
2000020001																															10000(30)0000	1	0	0	0	0	30	0	0	0	0
001012141																															000103000	0	0	0	1	0	3	0	0	0
000000000																															20112002x	2	0	1	1	2	0	0	2	x
2100101000001																															0002020010000	0	0	0	2	0	2	0	0	1	0	0	0	0
000000000																															00000100x	0	0	0	0	0	1	0	0	x
000001010																															00011100x	0	0	0	1	1	1	0	0	x
010100010																															000100001	0	0	0	1	0	0	0	0	1
004100000																															000000010	0	0	0	0	0	0	0	1	0
001000000																															00204101x	0	0	2	0	4	1	0	1	x
001000010																															02000010x	0	2	0	0	0	0	1	0	x
2000000202																															1000001200	1	0	0	0	0	0	1	2	0	0
000000200																															02100000x	0	2	1	0	0	0	0	0	x

 

[Peter_SSs]

I must be missing something since as far as I can see the formula from post 4 (though certainly not the shortest formula suggested) works with all the scenarios posted.
Here it is again with the latest layout/sample data & showing no issues where there is other data to the right (provided there are sufficient empty columns to house the results)

mad3.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH																AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
3	V-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13	14																30	H-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13
4	100000000	1	0	0	0	0	0	0	0	0																						00121040x	0	0	1	2	1	0	4	0	x
5	00010000000	0	0	0	1	0	0	0	0	0	0	0																				00000(10)00001	0	0	0	0	0	10	0	0	0	0	1
6	010203100	0	1	0	2	0	3	1	0	0																						000011020	0	0	0	0	1	1	0	2	0
7	000011001	0	0	0	0	1	1	0	0	1																						00010510x	0	0	0	1	0	5	1	0	x
8	001300000	0	0	1	3	0	0	0	0	0																						00011021x	0	0	0	1	1	0	2	1	x
9	2000020001	2	0	0	0	0	2	0	0	0	1																					10000(30)0000	1	0	0	0	0	30	0	0	0	0
10	001012141	0	0	1	0	1	2	1	4	1																						000103000	0	0	0	1	0	3	0	0	0
11	000000000	0	0	0	0	0	0	0	0	0																						20112002x	2	0	1	1	2	0	0	2	x
12	2100101000001	2	1	0	0	1	0	1	0	0	0	0	0	1																		0002020010000	0	0	0	2	0	2	0	0	1	0	0	0	0
13	000000000	0	0	0	0	0	0	0	0	0																						00000100x	0	0	0	0	0	1	0	0	x
14	000001010	0	0	0	0	0	1	0	1	0																						00011100x	0	0	0	1	1	1	0	0	x
15	010100010	0	1	0	1	0	0	0	1	0																						000100001	0	0	0	1	0	0	0	0	1
16	004100000	0	0	4	1	0	0	0	0	0																						000000010	0	0	0	0	0	0	0	1	0
17	001000000	0	0	1	0	0	0	0	0	0																						00204101x	0	0	2	0	4	1	0	1	x
18	001000010	0	0	1	0	0	0	0	1	0																						02000010x	0	2	0	0	0	0	1	0	x
19	2000000202	2	0	0	0	0	0	0	2	0	2																					1000001200	1	0	0	0	0	0	1	2	0	0
20	000000200	0	0	0	0	0	0	2	0	0																						02100000x	0	2	1	0	0	0	0	0	x
Sheet1

Cell Formulas
Range	Formula
U4:AC4,AZ20:BH20,AZ19:BI19,AZ13:BH18,AZ12:BL12,AZ10:BH11,AZ9:BI9,AZ6:BH8,AZ5:BJ5,AZ4:BH4,U20:AC20,U19:AD19,U13:AC18,U12:AG12,U10:AC11,U9:AD9,U6:AC8,U5:AE5	U4	=LET(ts,TEXTSPLIT(T4,{"(",")"}),TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "))
Dynamic array formulas.

 

[mad3]

I must be missing something since as far as I can see the formula from post 4 (though certainly not the shortest formula suggested) works with all the scenarios posted.
Here it is again with the latest layout/sample data & showing no issues where there is other data to the right (provided there are sufficient empty columns to house the results)

mad3.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH																AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
3	V-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13	14																30	H-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13
4	100000000	1	0	0	0	0	0	0	0	0																						00121040x	0	0	1	2	1	0	4	0	x
5	00010000000	0	0	0	1	0	0	0	0	0	0	0																				00000(10)00001	0	0	0	0	0	10	0	0	0	0	1
6	010203100	0	1	0	2	0	3	1	0	0																						000011020	0	0	0	0	1	1	0	2	0
7	000011001	0	0	0	0	1	1	0	0	1																						00010510x	0	0	0	1	0	5	1	0	x
8	001300000	0	0	1	3	0	0	0	0	0																						00011021x	0	0	0	1	1	0	2	1	x
9	2000020001	2	0	0	0	0	2	0	0	0	1																					10000(30)0000	1	0	0	0	0	30	0	0	0	0
10	001012141	0	0	1	0	1	2	1	4	1																						000103000	0	0	0	1	0	3	0	0	0
11	000000000	0	0	0	0	0	0	0	0	0																						20112002x	2	0	1	1	2	0	0	2	x
12	2100101000001	2	1	0	0	1	0	1	0	0	0	0	0	1																		0002020010000	0	0	0	2	0	2	0	0	1	0	0	0	0
13	000000000	0	0	0	0	0	0	0	0	0																						00000100x	0	0	0	0	0	1	0	0	x
14	000001010	0	0	0	0	0	1	0	1	0																						00011100x	0	0	0	1	1	1	0	0	x
15	010100010	0	1	0	1	0	0	0	1	0																						000100001	0	0	0	1	0	0	0	0	1
16	004100000	0	0	4	1	0	0	0	0	0																						000000010	0	0	0	0	0	0	0	1	0
17	001000000	0	0	1	0	0	0	0	0	0																						00204101x	0	0	2	0	4	1	0	1	x
18	001000010	0	0	1	0	0	0	0	1	0																						02000010x	0	2	0	0	0	0	1	0	x
19	2000000202	2	0	0	0	0	0	0	2	0	2																					1000001200	1	0	0	0	0	0	1	2	0	0
20	000000200	0	0	0	0	0	0	2	0	0																						02100000x	0	2	1	0	0	0	0	0	x
Sheet1

Cell Formulas
Range	Formula
U4:AC4,AZ20:BH20,AZ19:BI19,AZ13:BH18,AZ12:BL12,AZ10:BH11,AZ9:BI9,AZ6:BH8,AZ5:BJ5,AZ4:BH4,U20:AC20,U19:AD19,U13:AC18,U12:AG12,U10:AC11,U9:AD9,U6:AC8,U5:AE5	U4	=LET(ts,TEXTSPLIT(T4,{"(",")"}),TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "))
Dynamic array formulas.

I see you are getting results, but when I cut and paste the formula into cell U4, all I get is the formula, no result. I'm using excel 365 Enterprise so it should work based on result you are achieving. I don't know what is wrong.

 

[Peter_SSs]

when I cut and paste the formula into cell U4, all I get is the formula,

Sounds like U4 is formatted as 'Text'. Change the U4 format to 'General' and re-confirm the formula in that cell.

 

[mad3]

I must be missing something since as far as I can see the formula from post 4 (though certainly not the shortest formula suggested) works with all the scenarios posted.
Here it is again with the latest layout/sample data & showing no issues where there is other data to the right (provided there are sufficient empty columns to house the results)

mad3.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH																AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
3	V-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13	14																30	H-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13
4	100000000	1	0	0	0	0	0	0	0	0																						00121040x	0	0	1	2	1	0	4	0	x
5	00010000000	0	0	0	1	0	0	0	0	0	0	0																				00000(10)00001	0	0	0	0	0	10	0	0	0	0	1
6	010203100	0	1	0	2	0	3	1	0	0																						000011020	0	0	0	0	1	1	0	2	0
7	000011001	0	0	0	0	1	1	0	0	1																						00010510x	0	0	0	1	0	5	1	0	x
8	001300000	0	0	1	3	0	0	0	0	0																						00011021x	0	0	0	1	1	0	2	1	x
9	2000020001	2	0	0	0	0	2	0	0	0	1																					10000(30)0000	1	0	0	0	0	30	0	0	0	0
10	001012141	0	0	1	0	1	2	1	4	1																						000103000	0	0	0	1	0	3	0	0	0
11	000000000	0	0	0	0	0	0	0	0	0																						20112002x	2	0	1	1	2	0	0	2	x
12	2100101000001	2	1	0	0	1	0	1	0	0	0	0	0	1																		0002020010000	0	0	0	2	0	2	0	0	1	0	0	0	0
13	000000000	0	0	0	0	0	0	0	0	0																						00000100x	0	0	0	0	0	1	0	0	x
14	000001010	0	0	0	0	0	1	0	1	0																						00011100x	0	0	0	1	1	1	0	0	x
15	010100010	0	1	0	1	0	0	0	1	0																						000100001	0	0	0	1	0	0	0	0	1
16	004100000	0	0	4	1	0	0	0	0	0																						000000010	0	0	0	0	0	0	0	1	0
17	001000000	0	0	1	0	0	0	0	0	0																						00204101x	0	0	2	0	4	1	0	1	x
18	001000010	0	0	1	0	0	0	0	1	0																						02000010x	0	2	0	0	0	0	1	0	x
19	2000000202	2	0	0	0	0	0	0	2	0	2																					1000001200	1	0	0	0	0	0	1	2	0	0
20	000000200	0	0	0	0	0	0	2	0	0																						02100000x	0	2	1	0	0	0	0	0	x
Sheet1

Cell Formulas
Range	Formula
U4:AC4,AZ20:BH20,AZ19:BI19,AZ13:BH18,AZ12:BL12,AZ10:BH11,AZ9:BI9,AZ6:BH8,AZ5:BJ5,AZ4:BH4,U20:AC20,U19:AD19,U13:AC18,U12:AG12,U10:AC11,U9:AD9,U6:AC8,U5:AE5	U4	=LET(ts,TEXTSPLIT(T4,{"(",")"}),TEXTSPLIT(TEXTJOIN(" ",1,BYCOL(TEXT(SEQUENCE(,COLUMNS(ts)),"00")&ts,LAMBDA(c,IF(ISODD(LEFT(c,2)+0),TEXTJOIN(" ",1,MID(c,SEQUENCE(,99,3),1)),MID(c,3,2)))))," "))
Dynamic array formulas.

Amazing!
When I imported the file from net, I only brought over two columns as text, the V-linescore and H-linescore. However, I added the columns for the line score arrays to spill into. I managed to insert columns between the two Text formatted columns. Thus formatting the inserted columns as text. Ugh!
Formula now works just fine. Rick's and the other formulas work as well. Thanks so much!
Another lesson learned from the brain trust of this great program.

 

[mad3]

Here is a snapshot of a few selected rows from one of sheets. The H-LINESCORE works fine. No other data to right of column where formula is.
The V-LINESCORE would not work when placing the formula in cell U4 or any of the cells below that one. This same scenario happens on all spreadsheets for V-LINESCORE.

V-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	H-LINESCORE	1	2	3	4	5	6	7	8	9	10	11	12	13
100000000																															00121040x	0	0	1	2	1	0	4	0	x
00010000000																															00000(10)00001	0	0	0	0	0	10	0	0	0	0	1
010203100																															000011020	0	0	0	0	1	1	0	2	0
000011001																															00010510x	0	0	0	1	0	5	1	0	x
001300000																															00011021x	0	0	0	1	1	0	2	1	x
2000020001																															10000(30)0000	1	0	0	0	0	30	0	0	0	0
001012141																															000103000	0	0	0	1	0	3	0	0	0
000000000																															20112002x	2	0	1	1	2	0	0	2	x
2100101000001																															0002020010000	0	0	0	2	0	2	0	0	1	0	0	0	0
000000000																															00000100x	0	0	0	0	0	1	0	0	x
000001010																															00011100x	0	0	0	1	1	1	0	0	x
010100010																															000100001	0	0	0	1	0	0	0	0	1
004100000																															000000010	0	0	0	0	0	0	0	1	0
001000000																															00204101x	0	0	2	0	4	1	0	1	x
001000010																															02000010x	0	2	0	0	0	0	1	0	x
2000000202																															1000001200	1	0	0	0	0	0	1	2	0	0
000000200																															02100000x	0	2	1	0	0	0	0	0	x

View attachment 103089

Formula works just fine!
I managed to insert columns between linescores, thus Formatting new columns as text. Thanks so much for the help. And the suggestion to update profile. I need to get on forum regularly to keep up the learning from you gentlemen that are the brains of this great program!