Extracting characters before or after a specific character

B

Brian

Howdy All,

I have data imported from a web query, but it isn't formatted the way I need
it.

I have a part number and description combined in a cell, separated by --.

How do I extract the data before the -- and then the data following the -- ?

Thanks,
Brian
 
B

Beege

Brian said:
Howdy All,

I have data imported from a web query, but it isn't formatted the way I need
it.

I have a part number and description combined in a cell, separated by --.

How do I extract the data before the -- and then the data following the -- ?

Thanks,
Brian
Click to expand...

How about "Text to Columns", with -- as your delimiter?

Beege
 
J

JE McGimpsey

One way:

Choose Data/Text to Columns/Delimited. Click Next. Enter - in the Other
box, and check the "Treat consecutive delimiters as one" checkbox. Click
Finish.

With formulae:

Part number: =LEFT(A1,FIND("--",A1)-1)

Description: =MID(A1, FIND("--",A1)+2,32000)

(where 32000 is just a large enough number of characters to capture the
description)
 
B

Brian

Thanks for the input.

This is what I came up with:

Data from Sheet 3, Cell C2.

For data before -- =LEFT(Sheet3!C2,FIND("--",Sheet3!C2)-1)
For data after --
=RIGHT(Sheet3!C2,(LEN(Sheet3!C2)-FIND("--",Sheet3!C2))-2)
 
Top