Extracting text left of specific character, but before another specific character.

M

marduino

New Member
Joined
Dec 24, 2021
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
I am looking to extract horse names from text in a single cell (ie; A1,A2,A3...). I cannot use text to columns because the data before the name can vary (A9,A10 for example), along with other spacing issues in the data...
The only unique way that I can think of extracting this text would be searching LEFT of "( ", until finding a number {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}, but I am having trouble figuring out how to utilize LEFT/RIGHT/MID functions to achieve that.

1640351637251.png


EQUIBASE MACRO.xlsx
A
13Jun21 3CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad) 122 L b 5 3 31 1/2 32 11 13 1Neck 1.95* 3-2p trns,drift out1/8
229Jun21 7PRX5 8 Ekhtibaar (Santana, Jr., Ricardo) 122 L b 7 6 51/2 51 1/2 3Head 22 26 3/4 18.80 2-3w2nd,mvd out,gained
33Jun21 3CD1 4 Wagon Boss (Rosario, Joel) 126 L b 4 1 21/2 21/2 21 34 33 1/4 3.00 2path,ins3/8,tired
48May21 1BEL7 6 Flowers for Lisa (Saez, Luis) 124 L b 6 4 41 4Head 51 1/2 41 1/2 410 3.35 4wide turns,empty
520Jun21 6BEL8 2 Playwright (Cardenas, Luis) 122 L 2 5 8 8 73 1/2 61 1/2 5Neck 24.25 stumbled brk,bumped
612Jun21 3BEL4 9 Hammerin Aamer (Cohen, David) 124 L b 8 7 6Head 61 1/2 62 1/2 53 1/2 612 3.85 3w turns,no response
710Jul21 2BEL9 3 No Lime (Hernandez Moreno, Omar) 114 L f 3 8 72 1/2 72 8 8 710 16.40 bmpd brk,bobbld,stdy
82Jul21 4BEL5 1 Revenio (Gaffalione, Tyler) 122 L b 1 2 11 11/2 41 1/2 78 8 35.75 ins,headed1/2,stopped
9--- 5 Outfoxed (Alvarado, Junior) 119 - - 4 7 71/2 61/2 44 1/2 31 15.10 off 1/2 step slw,3-4w
10--- 8 Li Li Bear (Davis, Dylan) 119 - - 7 3 32 32 31/2 43 1/2 14.30 outside,3w turn,tired
Sheet3



Any help is appreciated, and thank you for your time.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
AB
1
23Jun21 3CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad) 122 L b 5 3 31 1/2 32 11 13 1Neck 1.95* 3-2p trns,drift out1/8Charlie'sarchangel
329Jun21 7PRX5 8 Ekhtibaar (Santana, Jr., Ricardo) 122 L b 7 6 51/2 51 1/2 3Head 22 26 3/4 18.80 2-3w2nd,mvd out,gainedEkhtibaar
43Jun21 3CD1 4 Wagon Boss (Rosario, Joel) 126 L b 4 1 21/2 21/2 21 34 33 1/4 3.00 2path,ins3/8,tiredWagon Boss
58May21 1BEL7 6 Flowers for Lisa (Saez, Luis) 124 L b 6 4 41 4Head 51 1/2 41 1/2 410 3.35 4wide turns,emptyFlowers for Lisa
620Jun21 6BEL8 2 Playwright (Cardenas, Luis) 122 L 2 5 8 8 73 1/2 61 1/2 5Neck 24.25 stumbled brk,bumpedPlaywright
712Jun21 3BEL4 9 Hammerin Aamer (Cohen, David) 124 L b 8 7 6Head 61 1/2 62 1/2 53 1/2 612 3.85 3w turns,no responseHammerin Aamer
810Jul21 2BEL9 3 No Lime (Hernandez Moreno, Omar) 114 L f 3 8 72 1/2 72 8 8 710 16.40 bmpd brk,bobbld,stdyNo Lime
92Jul21 4BEL5 1 Revenio (Gaffalione, Tyler) 122 L b 1 2 11 11/2 41 1/2 78 8 35.75 ins,headed1/2,stoppedRevenio
10--- 5 Outfoxed (Alvarado, Junior) 119 - - 4 7 71/2 61/2 44 1/2 31 15.10 off 1/2 step slw,3-4wOutfoxed
11--- 8 Li Li Bear (Davis, Dylan) 119 - - 7 3 32 32 31/2 43 1/2 14.30 outside,3w turn,tiredLi Li Bear
Lists
Cell Formulas
RangeFormula
B2:B11B2=REPLACE(LEFT(A2,FIND("(",A2)-2),1,FIND("^",SUBSTITUTE(A2," ","^",IF(ISNUMBER(MID(A2,FIND(" ",A2)+1,2)+0),2,3))),"")

This assumes that you cannot have a string like "3Jun21 32CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad)"
 
Upvote 0
One more

Excel Formula: 
=MID(A2,SEARCH(" ? *(",A2)+3,SEARCH("(",A2)-SEARCH(" ? *(",A2)-4)
 
Upvote 0
Hi

Excel Formula: 
=FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(A1,"---","1Jan00 1ALL1")," ","</z><z>")&"</z></y>","//z[position()=4]")
 
Upvote 0
I am not sure but I think this function is available since 2013?
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
AB
1
23Jun21 3CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad) 122 L b 5 3 31 1/2 32 11 13 1Neck 1.95* 3-2p trns,drift out1/8Charlie'sarchangel
329Jun21 7PRX5 8 Ekhtibaar (Santana, Jr., Ricardo) 122 L b 7 6 51/2 51 1/2 3Head 22 26 3/4 18.80 2-3w2nd,mvd out,gainedEkhtibaar
43Jun21 3CD1 4 Wagon Boss (Rosario, Joel) 126 L b 4 1 21/2 21/2 21 34 33 1/4 3.00 2path,ins3/8,tiredWagon Boss
58May21 1BEL7 6 Flowers for Lisa (Saez, Luis) 124 L b 6 4 41 4Head 51 1/2 41 1/2 410 3.35 4wide turns,emptyFlowers for Lisa
620Jun21 6BEL8 2 Playwright (Cardenas, Luis) 122 L 2 5 8 8 73 1/2 61 1/2 5Neck 24.25 stumbled brk,bumpedPlaywright
712Jun21 3BEL4 9 Hammerin Aamer (Cohen, David) 124 L b 8 7 6Head 61 1/2 62 1/2 53 1/2 612 3.85 3w turns,no responseHammerin Aamer
810Jul21 2BEL9 3 No Lime (Hernandez Moreno, Omar) 114 L f 3 8 72 1/2 72 8 8 710 16.40 bmpd brk,bobbld,stdyNo Lime
92Jul21 4BEL5 1 Revenio (Gaffalione, Tyler) 122 L b 1 2 11 11/2 41 1/2 78 8 35.75 ins,headed1/2,stoppedRevenio
10--- 5 Outfoxed (Alvarado, Junior) 119 - - 4 7 71/2 61/2 44 1/2 31 15.10 off 1/2 step slw,3-4wOutfoxed
11--- 8 Li Li Bear (Davis, Dylan) 119 - - 7 3 32 32 31/2 43 1/2 14.30 outside,3w turn,tiredLi Li Bear
Lists
Cell Formulas
RangeFormula
B2:B11B2=REPLACE(LEFT(A2,FIND("(",A2)-2),1,FIND("^",SUBSTITUTE(A2," ","^",IF(ISNUMBER(MID(A2,FIND(" ",A2)+1,2)+0),2,3))),"")

This assumes that you cannot have a string like "3Jun21 32CD2 5 Charlie'sarchangel (Ortiz, Jr., Irad)"
Click to expand...

Thank you, I thought this might work, but looking at more lines of data, there are unfortunately some entries with 2 digits here.
 
Upvote 0
Can you have a string like "3Jun21 3CD2 16 Charlie'sarchangel" which you implied was possible?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
367
felixstraube
F
D
  • Solved
List of items within a specific month
Replies
4
Views
218
Fluff
Fluff
B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
350
etaf
etaf
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S
Z
  • Solved
Extract text between characters
Replies
2
Views
278
zinah
Z

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

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