# Sort of like opposite of concatenate

#### Rosstamon

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
Hi,

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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 14
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

1,081,524
Messages
5,359,269
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...