Find text to the left of character

Using your posted workbook
this regular formula finds the cell containing the K1 text and returns the text to the left of that text

Please Login or Register  to view this content.

Is that something you can work with?

Hi cmb,

Here's some VBA:

Please Login or Register  to view this content.

Here is a different mix of functions that will give the results that you are looking for. The key is identifying what you are wanting to match. In this case it is the contents of K1 which could appear anywhere within the text string in a cell. The "*"&K1&"*", used by Ron Coderre and myself finds anything before K1 followed by anything after in the cell's text string. Match("*"&K1&"*",A1:A15,0) will by itself give the row number where the text in K1 is found. The "A"& in front of the MATCH produces a cell reference in text form where this match is. The Indirect is used to return the contents of that cell reference. LEFT is used to extract the contents of the cell that has the text from K1. It looks for the ( and then returns everything left of the ( so a -1 is added to the end of the LEFT function near the end of the formula. TRIM just removes all unnecessary spaces.

It was tempting to use a space instead of the ( but if there were to be more than 1 space (two words), the result would probably not be what you wanted. As long as there is a ( only around the search term and not before, this should work.

Formula:

Please Login or Register  to view this content.

@both Ron's - will your formula also get the next occurrence as does my routine?

We can only work with the facts we've been given. In the example, there were no duplicate entries.
That being said...
If I'd offered a solution that could return multiple occurrences...somebody would have come along with something shorter, touting it's brevity.
Instead, I offered a solution that assumes there is only one occurrence...so of course... :|

The formulas will return the first instance. There was nothing to indicate that anything else was wanted.

Maybe this

=IFERROR(INDEX(LEFT($A$1:$A$13,FIND(" (",$A$1:$A$13)-1),SMALL(IF(ISNUMBER(SEARCH("*"&SUBSTITUTE(SUBSTITUTE(K$1,"(",""),")",""),$A$1:$A$13)),ROW($A$1:$A$13)),ROWS(B$1:B1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B
1	CHEPSTOW	CAULFIELD
2	FAIRYHOUSE (IRE)	RANDWICK
3	HEXHAM
4	MUSSELBURGH
5	NEWMARKET
6	WOLVERHAMPTON (AW)
7	YORK
8	CAULFIELD (AUS)
9	HAWERA (NZ)
10	SAN ISIDRO (ARG)
11	KEENELAND (USA)
12	MAISONS-LAFFITTE (FR)
13	RANDWICK (AUS

@ Ron Coderre - what does the colon pipe signify? - I haven't seen it before

@ Ron W - If the formula were dragged down, it would keep returning the first occurrence n'est pas?

I think it is like the Mona Lisa....neither a smile nor a frown :/ (a crooked smile)

Originally Posted by xladept

@ Ron W - If the formula were dragged down, it would keep returning the first occurrence n'est pas?

Of course, it wasn't intended to do anything else.

It's the face you make when you make a puzzling observation and you say: Hmmmm

Hmmmmm :| Crooked smile :/ Great thanks guys :\