Return the text before a | in a text string

  • Thread starter Thread starter ArcticWolf
  • Start date Start date
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
 
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
 
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
 
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.
 
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
 
Back
Top