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

[marduino]

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.

EQUIBASE MACRO.xlsx
	A
1	3Jun21 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
2	29Jun21 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
3	3Jun21 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
4	8May21 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
5	20Jun21 6BEL8 2 Playwright (Cardenas, Luis) 122 L 2 5 8 8 73 1/2 61 1/2 5Neck 24.25 stumbled brk,bumped
6	12Jun21 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
7	10Jul21 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
8	2Jul21 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.

 

[marduino]

Can you have a string like "3Jun21 3CD2 16 Charlie'sarchangel" which you implied was possible?

Yes,

Theoretically, could have instances where any 2 numbers are in these positions.

"##Jun## ##CD## ## Charlie'sarchangel"

 

[marduino]

This does the trick, thank you!

hmm, spoke too soon, did not consider all of the possible conditions

 

[Fluff]

Ok, how about

=REPLACE(LEFT(A2,FIND("(",A2)-2),1,IFERROR(SEARCH(" ? *(",A2)+2,SEARCH(" ?? *(",A2)+3),"")

 

[marduino]

Ok, how about

=REPLACE(LEFT(A2,FIND("(",A2)-2),1,IFERROR(SEARCH(" ? *(",A2)+2,SEARCH(" ?? *(",A2)+3),"")

This looks to be working for all scenarios I can come up with, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.