i have a very long column in Excel that has the following type of
information in each cell:

Bosch|Dish|Dishwasher|Bosch Dishwasher

I'm trying to use FIND, LEFT, LEN, RIGHT, and IF to extract the word
"Dish" from the string of text.

I have a ton of these cells and they're all different, but I only need
the word left of the first "|" mark and right of the second "|" mark.

here are a few more examples of the text in each cell

Bosch|Cooking|Oven|Double|Bosch Double Convection Oven - I only want
"Cooking"

LG|Dish|Dishwasher|LG Built-In Dishwasher - I only want "Dish"

Electrolux|Fabric Care|Washer|electrolux washing machine - I only want
"Fabric Care"

Could anyone help me?

Brady

Try:
=MID(A1,SEARCH("|",A1)+1,(SEARCH("|",A1,SEARCH("|" ,A1)+1))-(SEARCH("|",A1)+1))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


wrote in message
oups.com...
i have a very long column in Excel that has the following type of
information in each cell:

Bosch|Dish|Dishwasher|Bosch Dishwasher

I'm trying to use FIND, LEFT, LEN, RIGHT, and IF to extract the word
"Dish" from the string of text.

I have a ton of these cells and they're all different, but I only need
the word left of the first "|" mark and right of the second "|" mark.

here are a few more examples of the text in each cell

Bosch|Cooking|Oven|Double|Bosch Double Convection Oven - I only want
"Cooking"

LG|Dish|Dishwasher|LG Built-In Dishwasher - I only want "Dish"

Electrolux|Fabric Care|Washer|electrolux washing machine - I only want
"Fabric Care"

Could anyone help me?

Brady

Assuming the data is in A1, exact same thing except using FIND:

=MID(A1,FIND("|",A1,1)+1,FIND("|",A1,FIND("|",A1,1 )+1)-
FIND("|",A1,1)-1)

Since case doesn't matter, you can use either one. I'm not sure which
is more efficient - if either, it wouldn't be noticeable.


On Jul 17, 5:13 pm, wrote:
i have a very long column in Excel that has the following type of
information in each cell:

Bosch|Dish|Dishwasher|Bosch Dishwasher

I'm trying to use FIND, LEFT, LEN, RIGHT, and IF to extract the word
"Dish" from the string of text.

I have a ton of these cells and they're all different, but I only need
the word left of the first "|" mark and right of the second "|" mark.

here are a few more examples of the text in each cell

Bosch|Cooking|Oven|Double|Bosch Double Convection Oven - I only want
"Cooking"

LG|Dish|Dishwasher|LG Built-In Dishwasher - I only want "Dish"

Electrolux|Fabric Care|Washer|electrolux washing machine - I only want
"Fabric Care"

Could anyone help me?

Brady

wrote...
....
I have a ton of these cells and they're all different, but I only
need the word left of the first "|" mark and right of the second
"|" mark.
....

Looks like you mean between the 1st and 2nd |s. Try

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

Harlan Grove wrote...
....
=REPLACE(LEFT(A1,FIND("|",A1,FIND("|",A1)+1)-1),1,FIND("|",A1),"")

Or shorter but with hardcoded #s,

=MID(LEFT(A1,FIND("|",A1,FIND("|",A1)+1)-1),FIND("|",A1)+1,1024)