String Manipulation

[Frank Bacchus]

Hi:

I have a text string and would like to 'chop off' some of the letters on the right. The string is like this: Home Sugar and baking powder;#15

The delimiter that is common is ; Upon completion, I would like to see : Home Sugar and baking powder.

How do I construct the formula? Thanks.

frank

 

[p45cal]

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

Or you can do it in situ with Text to Columns:
Bring up the dialogue box, choose Delimited, click Next, put a tick in the Semicolon box and untick everything else, click Next, click at the top of the second column to highlight that whole column in the bottom half of the dialogue box, choose Do not import (skip) and click Finish.

 

[bosco_yip]

Another option.

If the string doesn't contain ";" and the formula return the string.

=LEFT(A1,FIND(";",A1&";")-1)

Regards
Bosco