Remove text from cell

Ben

New Member
Joined
May 23, 2002
Messages
37
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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," ",""))))),"")
 
Upvote 0
Thanks all for your help the LEFT LEN calculation does the business as well.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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