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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
@shift-del the Op is using 2010, which doesn't have the filterxml function.
 
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

M
  • Question
Need Help Extracting Price from Text Strings of Various Lengths
Replies
2
Views
477
Micron
M
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
155
DanteAmor
DanteAmor
V
  • Question
Request help in extracting mutiple number groups from specific character positions from a varying number size.
Replies
3
Views
134
Fluff
Fluff
M
  • Solved
Find (x) number copy some left cell and paste into another location
Replies
6
Views
215
Alex Blakenburg
A
P
  • Question
Offset Processes
Replies
7
Views
271
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,011
Messages
6,122,677
Members
449,092
Latest member
tayo4dgacorbanget

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