Remove text from cell

[Ben]

I saw an answer to a query posted earlier which is almost the same as a problem I have.
I have text in a cell that looks as follows: The rows go down about 1000 rows.

ABCD_YZ1234_123456789

_ is a space.

After the second space there will always be 9 digits.
I want to be able to strip those last 9 digits and the second space out of the string leaving only the remaining text to the left.

The mid section e.g. YZ1234 could be variable up to a between 3 and 6 characters. This means i can not just use LEFT(A1,9) as I will not always get the variable mid section.
the spaces will always be the same.

A previous post offered

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

This I have been tweaking to try to sort my problem but I can't quite nail it.
Any ideas?

Thanks in advance

 

[Aladin Akyurek]

If you download & install the morefunc.xll add-in, you can have...

=SUBSTITUTE(A1," "&WMID(A1,WORDCOUNT(A1)),"")

Otherwise, use the generic...

=SUBSTITUTE(A1," "&RIGHT(A1,LEN(A1)-SEARCH("@#",SUBSTITUTE(A1," ","@#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")

 

[Ben]

Aladin
Thanks - Option 2 worked a treat. Thanks for your assistance

 

[inarbeth]

=LEFT(A1,LEN(A1)-10)

or am I missing something.

 

[Albert 1]

=LEFT(A1,(LEN(A1)-10))

Or, without the first space:
=SUBSTITUTE(LEFT(A1,LEN(A1)-10)," ","",1)

 

[Aladin Akyurek]

=LEFT(A1,LEN(A1)-10)

or am I missing something.

You're not missing anything. I opted for a generic solution, meaning "no preset constant(s) in the formula".

 

[Ben]

Thanks all for your help the LEFT LEN calculation does the business as well.