Return the text before a | in a text string

[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

 

[Gary''s Student]

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

 

[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

 

[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

 

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

 

[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