Sort of like opposite of concatenate

Rosstamon

Board Regular
Joined
Sep 12, 2007
Messages
77
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
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
 
Upvote 0
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
ABCD
9Backplate 1-2 weeks 14Backplate1-2 weeks14
10Carbon Wall Mounts 1-2 weeks 14Carbon Wall Mounts1-2 weeks14
11AP Mount 1-2 weeks 14AP Mount1-2 weeks14
12AP Power Pack Mount 1-2 weeks 14AP Power Pack Mount1-2 weeks14
Separate
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top