TEXTAFTER with textbefore...to extract number or series of numbers.

[mad3]

I have a spreadsheet with text in one column that also has a number or series of numbers at the end of each line. I want to be able to extract the number or series of numbers individually into each of the columns to the right. When there are more than one number, there can be as many as a series of 6 numbers. On the

Column1

New York (A) - Top of 1st

Zac Gallen pitching for Boston

Bryan Reynolds flied out to right. (919)

Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)

Bo Bichette singled to left. (379, 41)

Jeimer Candelario flied out to center. (968)

0 runs, 1 hits, 0 errors

Boston - Bottom of 1st

Max Fried pitching for New York (A)

Harold Ramirez struck out swinging. (874)

Jose Ramirez singled to left. (245)

Ronald Acuna Jr. walked, Jose Ramirez to second. (244)

Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald Acuna Jr. scored. (144)

Wilmer Flores grounded out to third. (296)

Jorge Soler popped out to catcher. (527, 13, 5)

3 runs, 2 hits, 0 errors

New York (A) - Top of 2nd

Zac Gallen pitching for Boston

Joey Meneses singled to right. (163)

Yan Gomes struck out swinging. (230)

Whit Merrifield doubled to left center, Joey Meneses scored. (932)

Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)

Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)

Bryan Reynolds grounded out to first. (927)

The example of what I am looking for is to extract the 919 from line three.
to extract the (726, 30, 36, 17, 97, 31) from line four into six columns.

 

[mad3]

You probably need to replace that semi-colon in the formula with a comma. However it will have the same "table" issue as the others.

Try this. I have added a couple of bits of data at the end of the table. I am not sure if data like this is possible for you but if so, the earlier formulas also do not check that the extracted numbers are from the end of the text as you indicated in post 1.

mad3.xlsm
	A	B	C	D	E	F	G
1	Data	No.1	No.2	No.3	No.4	No.5	No.6
2	New York (A) - Top of 1st
3	Zac Gallen pitching for Boston
4	Bryan Reynolds flied out to right. (919)	919
5	Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)	726	30	36	17	97	31
6	Bo Bichette singled to left. (379, 41)	379	41
7	Jeimer Candelario flied out to center. (968)	968
8	0 runs, 1 hits, 0 errors
9	Boston - Bottom of 1st
10	Max Fried pitching for New York (A)
11	Harold Ramirez struck out swinging. (874)	874
12	Jose Ramirez singled to left. (245)	245
13	Ronald Acuna Jr. walked, Jose Ramirez to second. (244)	244
14	Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald Acuna Jr. scored. (144)	144
15	Wilmer Flores grounded out to third. (296)	296
16	Jorge Soler popped out to catcher. (527, 13, 5)	527	13	5
17	3 runs, 2 hits, 0 errors
18	New York (A) - Top of 2nd
19	Zac Gallen pitching for Boston
20	Joey Meneses singled to right. (163)	163
21	Yan Gomes struck out swinging. (230)	230
22	Whit Merrifield doubled to left center, Joey Meneses scored. (932)	932
23	Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)	83	3	15
24	Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)	495	20
25	Bryan Reynolds grounded out to first. (927)	927
26	Charlie Blackmon (56) flied out to deep right. (726, 30, 36, 17, 97, 31)	726	30	36	17	97	31
27	Charlie Blackmon (56) flied out to deep right.
Sheet1

Cell Formulas
Range	Formula
B2:B27	B2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),1),""),"")
C2:C27	C2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),2),""),"")
D2:D27	D2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),3),""),"")
E2:E27	E2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),4),""),"")
F2:F27	F2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),5),""),"")
G2:G27	G2	=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),6),""),"")

This works just fine.

 

[Peter_SSs]

This works just fine.

Thanks for letting us know.

 

[mad3]

This works just fine.

You were right about the first not making sure about the numbers being at the end. In some cases, there are text that appears on the line somewhere before the number and in brackets. In those instances the prior formula would grab that text. Thanks again.