Extracting characters before or after a specific character
 

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

Answer: Extracting characters before or after a specific character
 

Hi Brian,

No worries, I can definitely help you with that. Here's how you can extract the data before and after the "--" in your combined cell:

1. First, select the cell that contains the combined data you want to split.
2. Next, go to the "Data" tab in the Excel ribbon and click on "Text to Columns".
3. In the "Convert Text to Columns Wizard" that appears, select "Delimited" and click "Next".
4. In the next screen, select the delimiter that separates your part number and description (in this case, "--") and click "Next".
5. Finally, choose the format for each column (e.g. General, Text, Date, etc.) and select the destination cell where you want to place the split data. Click "Finish" and you're done!

Alternatively, you can use the LEFT and RIGHT functions to extract the data before and after the "--" respectively. Here's how:

1. To extract the data before "--", use the LEFT function. In a new cell, type (assuming your combined data is in cell A1). This formula finds the position of the "--" in the cell and then extracts all the characters to the left of it.

2. To extract the data after "--", use the RIGHT function. In a new cell, type (again, assuming your combined data is in cell A1). This formula finds the position of the "--" in the cell and then extracts all the characters to the right of it.

Hope this helps!

Extracting characters before or after a specific character
 

Brian wrote:
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



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

Beege

Extracting characters before or after a specific character
 

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)

In article ,
"Brian" wrote:

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

Extracting characters before or after a specific character
 

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)


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