Return the text before a | in a text string

A

ArcticWolf

Hi,

I have a text string and would like all the characters before the | symbol
to be returned. The text before the | is varying lengths.

EG
Pears|Apples.........would return Pears
Bananas|Oranges...........would return Bananas

TIA,

AW
 
F

Francis

Hi try this

Assuming that your data are in A2 onward with the first row
being the header.
In B2, place this formula and copy down
=LEFT(A2,SEARCH("|",A2)-1)
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
J

Jacob Skaria

Just to add on.. Why dont you try Text to columns option. If you havent tried
select the range/column needs to be changed.
--From menu Data>Text to Columns will populate the 'Convert Text to Columns
Wizard'. By default the selection is 'Delimited'. Keep the selection and hit
'Next'.
--From the Step2 of the Wizard from the options in Other Specify the pipe
symbol and hit Next.
--Hit Finish

If this post helps click Yes
 
R

Rick Rothstein

Is it possible for there to be blank cells in the column with your pipe(|)
delimited text? If so, use this...

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

One side effect to this... if you have a piece of text without the pipe
symbol in it, the entire text from the cell will be returned.
 
A

ArcticWolf

Good tip Jacob. The original is on another worksheet so I've done a little
formula to bring it in.

Thanks for the heads up.

AW
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top