Delete a string

[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 _.

 

[Sean Timmons]

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

 

[Rick Rothstein]

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.

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[Mike H]

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 _.

 

[Mike H]

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