removing text after a character

[Kim]

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

 

[CLR]

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

 

[RagDyer]

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!
==============================================

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

 

[Gord Dibben]

Kim

Try Data>Text 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

 

[Ron Rosenfeld]

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