Delete a string

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

Joe Gieder

I have a long list of numbers taht all look similar to this M7885/2-4-02_- x
2 andI want to remove everything from the _ to the end. I think I want to use
Find, Replace and Length in a formula to get only the first part. One thing
is the length varies behin the _.
 
You can do 2 things..

1) Find, enter "_*" without the quote , Replace, leave the space blank.
Replace All

The * will remove everything after _

Or, to make it a formula, =LEFT(A2,FIND("_",A@)-1) and drag to bottom
 
If you want to show everything in front of the underbar in a different
column, you can use this formula...

=LEFT(A1,FIND("_",A1)-1)

If you want to physically modify the existing entry, you will need a VBA
macro for that.
 
Joe Gieder said:
I have a long list of numbers taht all look similar to this M7885/2-4-02_- x
2 andI want to remove everything from the _ to the end. I think I want to use
Find, Replace and Length in a formula to get only the first part. One thing
is the length varies behin the _.
Click to expand...
 
Hi,

This will leave the text you want in the existing column

Data|Text to columns
Select delimited
Next
Check 'Other' and enter an underscore in the 'other' box
Finish

If you don't want to keep the trailing bit then delete the column.

Mike
 
Back
Top