Macro to cut everything but first two words and paste to the column to the right

[danknacity]

Hi All,

New to Macros (Excel for Mac 2011), but am hoping someone can hep me out. I am looking to take a large and growing database of names followed by string of text all in column A and have them separated into two cells

I am currently dealing with the following (names have been changed for privacy)

Column A

A1: Trey Anastasio is cool
A2: Mike Gordon likes to eat ham.
A3: Paige McConnell is not very good at writing excel macros.

I would like to have the return look like:

Column A Column B
A1: Trey Anastasio B1: is cool
A2: Mike Gordon B2: likes to eat ham.
A3: Paige McConnell B3:is not very good at writing excel macros.

I have scoured the internet with no luck. Can someone please assist me?

Thank you in advance. I very much appreciate your help.

 

[texasalynn]

[TABLE="class: grid, width: 379"]
<tbody>[TR]
[TD="width: 147, bgcolor: transparent, align: left"]Col A
[/TD]
[TD="width: 108, bgcolor: transparent, align: left"]Col B
[/TD]
[TD="width: 250, bgcolor: transparent, align: left"]Col C
[/TD]
[/TR]
[TR]
[TD="width: 147, bgcolor: transparent, align: left"] Trey Anastasio is cool
[/TD]
[TD="width: 108, bgcolor: transparent, align: left"]Trey Anastasio[/TD]
[TD="width: 250, bgcolor: transparent, align: left"]is cool
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Mike Gordon likes to eat ham.[/TD]
[TD="bgcolor: transparent, align: left"]Mike Gordon[/TD]
[TD="bgcolor: transparent, align: left"]likes to eat ham.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Paige McConnell is not very good at writing excel macros.[/TD]
[TD="bgcolor: transparent, align: left"]Paige McConnell
[/TD]
[TD="bgcolor: transparent, align: left"]is not very good at writing excel macros. [/TD]
[/TR]
</tbody>[/TABLE]

formula
B1 =LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",2)))
C1=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",2))+1,99)

 

[danknacity]

Thanks Texasalynn, is there anyway to have this outcome via Macro, or is my best bet to just fill down?

[TABLE="class: grid, width: 379"]
<tbody>[TR]
[TD="width: 147, bgcolor: transparent, align: left"]Col A[/TD]
[TD="width: 108, bgcolor: transparent, align: left"]Col B[/TD]
[TD="width: 250, bgcolor: transparent, align: left"]Col C[/TD]
[/TR]
[TR]
[TD="width: 147, bgcolor: transparent, align: left"] Trey Anastasio is cool[/TD]
[TD="width: 108, bgcolor: transparent, align: left"]Trey Anastasio[/TD]
[TD="width: 250, bgcolor: transparent, align: left"]is cool[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Mike Gordon likes to eat ham.[/TD]
[TD="bgcolor: transparent, align: left"]Mike Gordon[/TD]
[TD="bgcolor: transparent, align: left"]likes to eat ham.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Paige McConnell is not very good at writing excel macros.[/TD]
[TD="bgcolor: transparent, align: left"]Paige McConnell[/TD]
[TD="bgcolor: transparent, align: left"]is not very good at writing excel macros.[/TD]
[/TR]
</tbody>[/TABLE]

formula
B1 =LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",2)))
C1=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",2))+1,99)

 

[texasalynn]

here you go

Sub Split_Cell()
Dim i As Long, LR As Long
Dim Part1 As String, Part2 As String

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Part1 = _
        Left(Cells(i, 1), WorksheetFunction.Find("|", WorksheetFunction.Substitute(Cells(i, 1), " ", "|", 2)) - 1)
    Part2 = _
        Mid(Cells(i, 1), WorksheetFunction.Find("|", WorksheetFunction.Substitute(Cells(i, 1), " ", "|", 2)) + 1, 99)
    Cells(i, 1) = Part1
    Cells(i, 2) = Part2
Next i
MsgBox "Done"
End Sub

 

[danknacity]

Thank you so much! Unfortunately I am getting a bug. It gets through most of a 20 row document but 19 and 20 don't work.

Error message: Run-time error '1004':
Method 'Find' of object 'WorksheetFunction' failed

Debug highlights the following lines:

Part1 = _
Left(Cells(i, 1), WorksheetFunction.Find("|", WorksheetFunction.Substitute(Cells(i, 1), " ", "|", 2)) - 1)|

Any suggestion how to fix it?

here you go

Sub Split_Cell()
Dim i As Long, LR As Long
Dim Part1 As String, Part2 As String

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    Part1 = _
        Left(Cells(i, 1), WorksheetFunction.Find("|", WorksheetFunction.Substitute(Cells(i, 1), " ", "|", 2)) - 1)
    Part2 = _
        Mid(Cells(i, 1), WorksheetFunction.Find("|", WorksheetFunction.Substitute(Cells(i, 1), " ", "|", 2)) + 1, 99)
    Cells(i, 1) = Part1
    Cells(i, 2) = Part2
Next i
MsgBox "Done"
End Sub

 

[texasalynn]

post the sample of those line

 

[danknacity]

Running the Macro on:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Trey Anastasio is Ccool[/TD]
[/TR]
[TR]
[TD]Mike Gordon likes to eat Ham[/TD]
[/TR]
[TR]
[TD]Paige McConnell is not very good at writing macros[/TD]
[/TR]
</tbody>[/TABLE]

Returns the following

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Trey Anastasio is cool[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike Gordon[/TD]
[TD]likes to eat ham[/TD]
[/TR]
[TR]
[TD]Paige McConnell[/TD]
[TD]is not very good at writing macros[/TD]
[/TR]
</tbody>[/TABLE]


In effort to fix the top one, I try running again and get the error from before.

I greatly appreciate all your help on this. Let me know if anything additional will help clarify

post the sample of those line

 

[texasalynn]

so you posted that 19 and 20 aren't working, but your sample is the same as your originals. So I'm not sure what your issue is so I can't answer

 

[danknacity]

I did not want to use the actual data as it is confidential. I am having the issue I just described. First line isn't working. Went to run the Macro again when 1 line wasn't separated but the two below were and got the error message and debug info described earlier.

so you posted that 19 and 20 aren't working, but your sample is the same as your originals. So I'm not sure what your issue is so I can't answer

 

[texasalynn]

so if your data starts on line one then the macro wasn't setup for that you would need to change the
For i = 1 to LR

I can't tell you any more without knowing more. I was interesting in seeing if perhaps there wasn't the same amount of spaces in the data.