Remove text from cell

Thanks:  0
Likes:  0

# Thread: Remove text from cell

1. ## Remove text from cell

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?

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

Otherwise, use the generic...

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

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

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

or am I missing something.

5. Originally Posted by inarbeth
=LEFT(A1,(LEN(A1)-10))
Or, without the first space:
=SUBSTITUTE(LEFT(A1,LEN(A1)-10)," ","",1)

6. Originally Posted by inarbeth
=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".

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•