Sort of like opposite of concatenate

[Rosstamon]

My data looks like this:

Cell A9 - Backplate 1-2 weeks 14
Cell A10 - Carbon Wall Mounts 1-2 weeks 14
Cell A11 - AP Mount 1-2 weeks 14
Cell A12 - AP Power Pack Mount 1-2 weeks 14

I want to do some kind of text to columns or formula or something that will leave me with:

Cell A9 - Backplate | Cell B9 - 1-2 weeks 14
Cell A10 - Carbon Wall Mounts | Cell B10 - 1-2 weeks 14
Cell A11 - AP Mount | Cell B11 - 1-2 weeks 14
Cell A12 - AP Power Pack Mount | Cell B12 - 1-2 weeks 14

Any ideas would be appreciated.

Thank you all,
R

 

[Mike Blackman]

Hi,

One way using formula in Col B & Col C;

in B9;

=LEFT(A9,FIND("-",A9)-3)

in C9;

=RIGHT(A9,LEN(A9)-FIND("-",A9)+2)

 

[Rosstamon]

It works. Thank you. I'll have to play with it a little because I made one mistake.

I also need to isolate the "14" in a seperate column. I may be able to play with the formulas you gave me and figure it out. But if you have a quick easy way, I would love to see it.

But thank you for this,
R

 

[Mike Blackman]

Hi,

Is the number always 14? Or always a specific lengh?

 

[Peter_SSs]

Rosstamon

You could see if these formulas (copied down) do what you want. Depending on the answer to Mikey's question, and just how uniform your full data really is, there may be an easier way, or a whole new approach might be needed.

Parts of your sample data seem very uniform. Apart from the 14 always being at the end, every example has '1-2 weeks'. If your data is actually more varied, it might be useful to post a more representative sample and the expected results.

Excel Workbook
	A	B	C	D
9	Backplate 1-2 weeks 14	Backplate	1-2 weeks	14
10	Carbon Wall Mounts 1-2 weeks 14	Carbon Wall Mounts	1-2 weeks	14
11	AP Mount 1-2 weeks 14	AP Mount	1-2 weeks	14
12	AP Power Pack Mount 1-2 weeks 14	AP Power Pack Mount	1-2 weeks	14
Separate