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

mad3

Board Regular
Joined
Sep 15, 2009
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Like this ?

Cell "B1"
Excel Formula:
=IFERROR(INT(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"("),")"),{", "," "})),"")

1707700311350.png
 
Upvote 0
Book1
ABCDEFG
1New York (A) - Top of 1st 
2Zac Gallen pitching for Boston 
3Bryan Reynolds flied out to right. (919)919
4Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)7263036179731
5Bo Bichette singled to left. (379, 41)37941
6Jeimer Candelario flied out to center. (968)968
70 runs, 1 hits, 0 errors 
8Boston - Bottom of 1st 
9Max Fried pitching for New York (A) 
10Harold Ramirez struck out swinging. (874)874
11Jose Ramirez singled to left. (245)245
12Ronald Acuna Jr. walked, Jose Ramirez to second. (244)244
13Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald 
14Acuna Jr. scored. (144)144
15Wilmer Flores grounded out to third. (296)296
16Jorge Soler popped out to catcher. (527, 13, 5)527135
173 runs, 2 hits, 0 errors 
18New York (A) - Top of 2nd 
19Zac Gallen pitching for Boston 
20Joey Meneses singled to right. (163)163
21Yan Gomes struck out swinging. (230)230
22Whit Merrifield doubled to left center, Joey Meneses scored. (932)932
23Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)83315
24Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)49520
25Bryan Reynolds grounded out to first. (927)927
Sheet1
Cell Formulas
RangeFormula
B1:B3,B6:B15,B17:B22,B25,B24:C24,B23:D23,B16:D16,B5:C5,B4:G4B1=IFERROR(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"("),")"),", "),"")
 
Last edited:
Upvote 0
Book1
ABCDEFG
1New York (A) - Top of 1st 
2Zac Gallen pitching for Boston 
3Bryan Reynolds flied out to right. (919)919
4Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)7263036179731
5Bo Bichette singled to left. (379, 41)37941
6Jeimer Candelario flied out to center. (968)968
70 runs, 1 hits, 0 errors 
8Boston - Bottom of 1st 
9Max Fried pitching for New York (A) 
10Harold Ramirez struck out swinging. (874)874
11Jose Ramirez singled to left. (245)245
12Ronald Acuna Jr. walked, Jose Ramirez to second. (244)244
13Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald 
14Acuna Jr. scored. (144)144
15Wilmer Flores grounded out to third. (296)296
16Jorge Soler popped out to catcher. (527, 13, 5)527135
173 runs, 2 hits, 0 errors 
18New York (A) - Top of 2nd 
19Zac Gallen pitching for Boston 
20Joey Meneses singled to right. (163)163
21Yan Gomes struck out swinging. (230)230
22Whit Merrifield doubled to left center, Joey Meneses scored. (932)932
23Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)83315
24Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)49520
25Bryan Reynolds grounded out to first. (927)927
Sheet1
Cell Formulas
RangeFormula
B1:B3,B6:B15,B17:B22,B25,B24:C24,B23:D23,B16:D16,B5:C5,B4:G4B1=IFERROR(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"("),")"),", "),"")
Thanks! This one worked as well. I just needed to figure out that it could not be used on a table.
 
Upvote 0
Hi

=IFERROR(TOROW(--TEXTSPLIT(A1,{"(",")",","});3),"")
 
Upvote 0
Like this ?

Cell "B1"
Excel Formula:
=IFERROR(INT(TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"("),")"),{", "," "})),"")

View attachment 106681
I am revisiting this to ask if there is a way to get this to work within a table? The reason I ask, I get all my data from the web and it automatically converts the data to a table when using GET DATA.
 
Upvote 0
Book1
ABCDEFG
1New York (A) - Top of 1st 
2Zac Gallen pitching for Boston 
3Bryan Reynolds flied out to right. (919)919
4Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)7263036179731
5Bo Bichette singled to left. (379, 41)37941
6Jeimer Candelario flied out to center. (968)968
70 runs, 1 hits, 0 errors 
8Boston - Bottom of 1st 
9Max Fried pitching for New York (A) 
10Harold Ramirez struck out swinging. (874)874
11Jose Ramirez singled to left. (245)245
12Ronald Acuna Jr. walked, Jose Ramirez to second. (244)244
13Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald 
14Acuna Jr. scored. (144)144
15Wilmer Flores grounded out to third. (296)296
16Jorge Soler popped out to catcher. (527, 13, 5)527135
173 runs, 2 hits, 0 errors 
18New York (A) - Top of 2nd 
19Zac Gallen pitching for Boston 
20Joey Meneses singled to right. (163)163
21Yan Gomes struck out swinging. (230)230
22Whit Merrifield doubled to left center, Joey Meneses scored. (932)932
23Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)83315
24Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)49520
25Bryan Reynolds grounded out to first. (927)927
Sheet1
Cell Formulas
RangeFormula
B1:B3,B6:B15,B17:B22,B25,B24:C24,B23:D23,B16:D16,B5:C5,B4:G4B1=IFERROR(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER(A1,"("),")"),", "),"")
I am revisiting this to ask if there is a way to get this to work within a table? The reason I ask, I get all my data from the web and it automatically converts the data to a table when using GET DATA.
 
Upvote 0
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
ABCDEFG
1DataNo.1No.2No.3No.4No.5No.6
2New York (A) - Top of 1st      
3Zac Gallen pitching for Boston      
4Bryan Reynolds flied out to right. (919)919     
5Charlie Blackmon flied out to deep right. (726, 30, 36, 17, 97, 31)7263036179731
6Bo Bichette singled to left. (379, 41)37941    
7Jeimer Candelario flied out to center. (968)968     
80 runs, 1 hits, 0 errors      
9Boston - Bottom of 1st      
10Max Fried pitching for New York (A)      
11Harold Ramirez struck out swinging. (874)874     
12Jose Ramirez singled to left. (245)245     
13Ronald Acuna Jr. walked, Jose Ramirez to second. (244)244     
14Aaron Judge homered to right center (438 feet), Jose Ramirez scored, Ronald Acuna Jr. scored. (144)144     
15Wilmer Flores grounded out to third. (296)296     
16Jorge Soler popped out to catcher. (527, 13, 5)527135   
173 runs, 2 hits, 0 errors      
18New York (A) - Top of 2nd      
19Zac Gallen pitching for Boston      
20Joey Meneses singled to right. (163)163     
21Yan Gomes struck out swinging. (230)230     
22Whit Merrifield doubled to left center, Joey Meneses scored. (932)932     
23Jake Meyers safe at first on error by first baseman Harold Ramirez, Whit Merrifield to third. (083, 3, 15)83315   
24Elvis Andrus flied out to left, Whit Merrifield scored. (495, 20)49520    
25Bryan Reynolds grounded out to first. (927)927     
26Charlie Blackmon (56) flied out to deep right. (726, 30, 36, 17, 97, 31)7263036179731
27Charlie Blackmon (56) flied out to deep right.      
Sheet1
Cell Formulas
RangeFormula
B2:B27B2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),1),""),"")
C2:C27C2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),2),""),"")
D2:D27D2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),3),""),"")
E2:E27E2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),4),""),"")
F2:F27F2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),5),""),"")
G2:G27G2=IF(RIGHT([@Data],1)=")",IFERROR(INDEX(--TEXTSPLIT(TEXTBEFORE(TEXTAFTER([@Data],"(",-1),")"),", "),6),""),"")
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top