Sort of like opposite of concatenate

Rosstamon

Board Regular
Joined
Sep 12, 2007
Messages
67
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
Joined
Jun 8, 2007
Messages
2,494
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
Joined
Sep 12, 2007
Messages
67
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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
 

Forum statistics

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

Some videos you may like

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...
Top