removing text after a character
 

Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior. I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you

Try highlighting the column, then doing Data Text to columns Delimited
and using / as the delimiter, and then highlighting the column after the
slash and choosing "do not import this column"

Just be sure you back up your data first and have a couple empty columns to
the right of your data column.......then Finish

Vaya con Dios,
Chuck, CAGEx3


"Kim" wrote in message
...
Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior.
I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you

Select the data, then,

<Data <Text To Columns

Check "Delimited", then <Next.

Click in the "other" box, and enter the slash [ / ]

You should see in the "Data Preview" window how your data will look.

Then <Next, and then select the second column in the "Preview" window.

Click on "Do Not Import Column", and you'll see the second column header
change to "Skip Column".

Then click <Finish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Kim" wrote in message
...
Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior. I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you

Kim

Try DataText to Columns delimited by the / mark. Enter / in "other"

You can choose to "do not import skip" the column after the / mark thus
leaving your data as you wish.


Gord Dibben Excel MVP

On Thu, 17 Feb 2005 16:25:02 -0800, Kim wrote:

Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior. I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you

On Thu, 17 Feb 2005 16:25:02 -0800, Kim wrote:

Hi all,

I have a list of colours in a column that look like the following examples

LIGHT BLUE/GREY
BLUE/GREY
CHERRY RED/BLACK
SILVER
LIGHT GREY

They represent vehicle exterior colours and after the "/" this is the
interior colour. As you can see some have the interior after the slash and
some don't have the interior colour but have two words for the exterior. I
need to extract the exterior colour only as the following.

LIGHT BLUE
BLUE
CHERRY RED
SILVER
LIGHT GREY

I have tried the "find" and the "substitute" formula's but can't manage to
get it right.

Please help

Thank you


With your data in A1, try:

=IF(COUNTIF(A1,"*/*"),LEFT(A1,FIND("/",A1)-1),A1)


--ron