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...

 

[Rick Rothstein]

Update your profile showing the version of Excel you are using so we know what functions we can use to design a formula for you.

 

[mad3]

Update your profile showing the version of Excel you are using so we know what functions we can use to design a formula for you.

I use Excel 365 for both Mac and PC. My primary computer is PC due to Excel having more options. How do I update profile to show version?

 

[Peter_SSs]

Does this do what you want for column Q?

23 12 02.xlsm
	Q		S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	0001010(11)0		0	0	0	1	0	1	0	11	0
2	00000010020		0	0	0	0	0	0	1	0	0	2	0
3	(10)9(11)(12)00000		10	9	11	12	0	0	0	0	0
4	25610(12)(11)6(10)		2	5	6	1	0	12	11	6	10
5	(10)(10)(10)(10)(10)(10)(10)(10)(10)(10)		10	10	10	10	10	10	10	10	10	10
To Columns

Cell Formulas
Range	Formula
S1:AA1,S5:AB5,S3:AA4,S2:AC2	S1	=LET(ts,TEXTSPLIT(Q1,{"(",")"}),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.

 

[JEC]

Another option where TEXT in combination with REPT is key

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	0001010(11)0	0	0	0	1	0	1	0	11	0
2	00000010020	0	0	0	0	0	0	1	0	0	2	0
3	(10)9(11)(12)00000	10	9	11	12	0	0	0	0	0
4	25610(12)(11)6(10)	2	5	6	1	0	12	11	6	10
5	(10)(10)(10)(10)(10)(10)(10)(10)(10)(10)	10	10	10	10	10	10	10	10	10	10
Sheet1

Cell Formulas
Range	Formula
B1:J1,B5:K5,B3:J4,B2:L2	B1	=DROP(REDUCE("",TEXTSPLIT(A1,{"(";")"},,1),LAMBDA(a,b,HSTACK(a,IF(LEN(b)=2,b,DROP(TEXTSPLIT(TEXT(b,REPT("0 ",LEN(b)))," "),,-1))))),,1)
Dynamic array formulas.

 

[mad3]

Another option where TEXT in combination with REPT is key

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	0001010(11)0	0	0	0	1	0	1	0	11	0
2	00000010020	0	0	0	0	0	0	1	0	0	2	0
3	(10)9(11)(12)00000	10	9	11	12	0	0	0	0	0
4	25610(12)(11)6(10)	2	5	6	1	0	12	11	6	10
5	(10)(10)(10)(10)(10)(10)(10)(10)(10)(10)	10	10	10	10	10	10	10	10	10	10
Sheet1

Cell Formulas
Range	Formula
B1:J1,B5:K5,B3:J4,B2:L2	B1	=DROP(REDUCE("",TEXTSPLIT(A1,{"(";")"},,1),LAMBDA(a,b,HSTACK(a,IF(LEN(b)=2,b,DROP(TEXTSPLIT(TEXT(b,REPT("0 ",LEN(b)))," "),,-1))))),,1)
Dynamic array formulas.

Both options worked perfectly! Awesome. This also worked for linescores that had more than 9 innings.

 

[mad3]

Does this do what you want for column Q?

23 12 02.xlsm
	Q		S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	0001010(11)0		0	0	0	1	0	1	0	11	0
2	00000010020		0	0	0	0	0	0	1	0	0	2	0
3	(10)9(11)(12)00000		10	9	11	12	0	0	0	0	0
4	25610(12)(11)6(10)		2	5	6	1	0	12	11	6	10
5	(10)(10)(10)(10)(10)(10)(10)(10)(10)(10)		10	10	10	10	10	10	10	10	10	10
To Columns

Cell Formulas
Range	Formula
S1:AA1,S5:AB5,S3:AA4,S2:AC2	S1	=LET(ts,TEXTSPLIT(Q1,{"(",")"}),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.

This worked perfectly!

 

[mad3]

Update your profile showing the version of Excel you are using so we know what functions we can use to design a formula for you.

I use Excel 365 for both Mac and PC. My primary computer is PC due to Excel having more options. How do I update profile to show version?

Thank you for enlightening me on changing my profile so the gurus can know what to use for formulas. Eye opening. Since I use both Mac and PC, I should have known because the MAC is limited, at times, with Excel formulas when it comes to the advanced stuff.

 

[Peter_SSs]

This also worked for linescores that had more than 9 innings.

As does the other one as you can see from rows 2 and 5 of the sample mini sheet.
Anyway you have choice. (edit: perhaps not - see next post)

BTW, thanks for updating your profile.

 

[Peter_SSs]

Both options worked perfectly!

Actually, checking again, are you sure?
What about this example?

23 12 02.xlsm
	A	B	C	D	E	F	G	H	I	J							Q		S	T	U	V	W	X	Y	Z	AA	AB
1	Using post 5 formula																Using post 4 formula
2	11(10)22(11)11(10)	11	10	22	11	11	10										11(10)22(11)11(10)		1	1	10	2	2	11	1	1	10
To Columns (3)

Cell Formulas
Range	Formula
B2:G2	B2	=DROP(REDUCE("",TEXTSPLIT(A2,{"(";")"},,1),LAMBDA(a,b,HSTACK(a,IF(LEN(b)=2,b,DROP(TEXTSPLIT(TEXT(b,REPT("0 ",LEN(b)))," "),,-1))))),,1)
S2:AA2	S2	=LET(ts,TEXTSPLIT(Q2,{"(",")"}),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.