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

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

--
Gary''s Student - gsnu200901

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



"ArcticWolf" wrote:

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

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 DataText 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
---------------
Jacob Skaria


"ArcticWolf" wrote:

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

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.

--
Rick (MVP - Excel)


"ArcticWolf" wrote in message
...
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

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

"Jacob Skaria" wrote:

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 DataText 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
---------------
Jacob Skaria


"ArcticWolf" wrote:

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