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]

Here is another formula that will work...

=TEXTSPLIT(TEXTJOIN(" ",,BYROW(WRAPROWS(TEXTSPLIT(Q1&")",{"(",")"}),2,""),LAMBDA(r,TRIM(CONCAT(MID(INDEX(r,1,1),SEQUENCE(99),1)&" ")&INDEX(r,1,2)))))," ")

 

[mad3]

Nice catch on that one scenerio with the 22.
have run into a snag in a couple other scenarios. First, if the home team doesn't hit in the bottom of the 9th and it shows an 'x', doesn't work. The other scenario is when there are cells to the columns/cells to the far right of the cell with the formula that are filled, it also fails. I initially cleared out 22 columns, but that was not enough as the formula still did not work. It worked when there were no occupied cells in any of the columns to the right of the formula.

 

[mad3]

Here is another formula that will work...

=TEXTSPLIT(TEXTJOIN(" ",,BYROW(WRAPROWS(TEXTSPLIT(Q1&")",{"(",")"}),2,""),LAMBDA(r,TRIM(CONCAT(MID(INDEX(r,1,1),SEQUENCE(99),1)&" ")&INDEX(r,1,2)))))," ")

Thanks. I am going to give this one a try in a few minutes.

 

[mad3]

Thanks. I am going to give this one a try in a few minutes.

Post #13 works as long as all columns to the right are empty after the formula cell.. It also has no issue with the 'x' for 9th inning where home team did not bat.
I may just remove all columns to the right after visiting team and split the home team linescore into a separate tble, then rejoin them with power query, if I have no other choice.

 

[mad3]

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.

Answering post #10: This one has issues with a few different scenarios. If cells are not all empty to right of formula cell, an 'x' in 9th inning or the scenario you showed in that post with the '22'...each of those scenarios did not work. I am thinking I may need to split the data in my table into two separate tables in order so that no cells to the right of the formula have anything in them.

 

[mad3]

Thanks. I am going to give this one a try in a few minutes.

post #13 second response: I used this formula for scenarios for over 2000 games and it worked for all games for home team. Home team is also in the last active/occupied column on sheet. Only scenario it won't work, the visiting team lines scores where there are cells to the right that are not empty. I made sure I had at least 30 empty cells to the right of visiting team to see if it would work, still no go.

 

[Rick Rothstein]

post #13 second response: I used this formula for scenarios for over 2000 games and it worked for all games for home team. Home team is also in the last active/occupied column on sheet. Only scenario it won't work, the visiting team lines scores where there are cells to the right that are not empty. I made sure I had at least 30 empty cells to the right of visiting team to see if it would work, still no go.

Your original file said, "I want to extract each individual number in column Q to separate columns", so I assumed you meant the columns to the right of the data cell. Yes, I know you showed data in Columns Q and R, but gave no instructions what columns you wanted the output to go to. As for the problem with how many cells to the right have to be empty... I think if you change the 99 (inside the SEQUENCE function) to the number of cells you can afford to make clear (but large enough to hold the outputted cells), I believe it will work as you need. For example, for the scenario where you cleared 30 cells, try changing the 99 to 30 (possibly one or two more), I think it should work.

 

[mad3]

Your original file said, "I want to extract each individual number in column Q to separate columns", so I assumed you meant the columns to the right of the data cell. Yes, I know you showed data in Columns Q and R, but gave no instructions what columns you wanted the output to go to. As for the problem with how many cells to the right have to be empty... I think if you change the 99 (inside the SEQUENCE function) to the number of cells you can afford to make clear (but large enough to hold the outputted cells), I believe it will work as you need. For example, for the scenario where you cleared 30 cells, try changing the 99 to 30 (possibly one or two more), I think it should work.

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!

 

[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!

Rick,
What I typically do when someone helps me out with something that is new to me, I review those functions/formulas I have not seen before, and learn how they are used and what each part is doing. Suffice to say, I have not seen or used SEQUENCE function before so I will be reviewing this and others to learn more about them. Thank you so much!

 

[felixstraube]

Another option with a recursive lambda function:

TextToColumnsWithOddItemsInBrackets.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA
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
Hoja1

Cell Formulas
Range	Formula
R1:Z1,R7:AA16,R4:Z6,R2:AA3	R1	=LET(str,myreplace(Q1,10), CODE(MID(str,SEQUENCE(,LEN(str)),1))-48 )
Dynamic array formulas.

Lambda Functions
Name	Formula
myreplace	=LAMBDA(str,i,IF(i<100,myreplace(SUBSTITUTE(str,"("&i&")",CHAR(48+i)),i+1),str))