split cells in a column by a sub-string and place the sub-string and its compliment in to adjacent cells on same row

capson

Board Regular
Joined
Jul 9, 2010
Messages
107
Hello,

I have a column of names on sheet1

Code:
Names
Tim Hanson
Bob Jones-Kelty
Hank Edward Williams

On sheet2 I have two columns First Name and Last Name in columns A & B
Code:
First Name    Last Name
Tim              Hanson
Bob              Jones-Kelty
Betty            Anderson
Hank            Edward Williams

I need to separate a column of Names on sheet1 column A into there First Name and Last Name based on if the Names contains the sub-strings First Name and Last Name

Need on sheet1
Code:
Names                            First Name             Last Name
Tim Hanson                       Tim                       Hanson
Bob Jones-Kelty                  Bob                       Jones-Kelty
Betty Anderson                 
Hank Edward Williams             Hank                     Edward Williams

I have search and been trying but have had no luck

Thanks for any assistance with this
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
So in your example, you want the macro to insert Betty Anderson's line and split the other lines.

Will the first/last names in Sheet2 be in the same order as in Sheet1?
 
Upvote 0
This should do what you want, you might want to add some Trim's
Code:
Sub test()
    Dim rngData1 As Range
    Dim rngData2 As Range
    Dim JoinedName As String
    Dim foundCell As Range
    
    Dim i As Long
    With ThisWorkbook.Sheets("Sheet1").Range("A:A")
        Set rngData1 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    With ThisWorkbook.Sheets("Sheet2").Range("A:A")
        Set rngData2 = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 2)
    End With
    
    For i = 1 To rngData2.Rows.Count
        JoinedName = rngData2.Cells(i, 1).Value & " " & rngData2.Cells(i, 2).Value
        Set foundCell = rngData1.Find(what:=JoinedName)
        If foundCell Is Nothing Then
            rngData1.Cells(i, 1).Insert shift:=xlDown
            rngData1.Cells(i, 1).Value = JoinedName
        Else
            foundCell.Offset(0, 1) = rngData2.Cells(i, 1)
            foundCell.Offset(0, 2) = rngData2.Cells(i, 2)
        End If
    Next i
    
End Sub
 
Last edited:
Upvote 0
Or without VBA, using a helper column

In Sheet 2
- add helper column C
- formula in C2 copied down
=A2&" "&B2

In Sheet 1
- formula in B2 copied down and across to column C
=INDEX(Sheet2!A:A,MATCH(Sheet1!$A2,Sheet2!$C:$C,0))

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Names[/td][td]First[/td][td]Second[/td][td]Formula in B2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]Tim Hanson[/td][td]Tim[/td][td]Hanson[/td][td]=INDEX(Sheet2!A:A,MATCH(Sheet1!$A2,Sheet2!$C:$C,0))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Bob Jones-Kelty[/td][td]Bob[/td][td]Jones-Kelty[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Hank Edward Williams[/td][td]Hank[/td][td]Edward Williams[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]First Name[/td][td]Last Name[/td][td]Helper[/td][td]
formula in column C
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]Tim[/td][td]Hanson[/td][td]Tim Hanson[/td][td]
=A2&" "&B2
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Bob[/td][td]Jones-Kelty[/td][td]Bob Jones-Kelty[/td][td]
=A3&" "&B3
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Betty[/td][td]Anderson[/td][td]Betty Anderson[/td][td]
=A4&" "&B4
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]Hank[/td][td]Edward Williams[/td][td]Hank Edward Williams[/td][td]
=A5&" "&B5
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
Thank you mikerickson, this work great. There is an edge case I did not account for. In sheet2 some people only have First Names and no Last Names

When this happens the output in sheet1 for these people have no output, but they need for the First Name to be added to Sheet1

Can this be revised for?

Ex

sheet1
Names
Tim

sheet2
Code:
First Name      Last Name
Tim

Thanks
 
Last edited:
Upvote 0
Thank you Yongle, I an not very familiar with formulas. Do you know is there a way to have the formula auto-expand as the range is dynamic
 
Last edited:
Upvote 0
Do you know is there a way to have the formula auto-expand as the range is dynamic
One way is to extend the formula further than current last row to allow for more rows of data (you may be using rows 2 to 200, but take the formula down to row 1000 if you will eventually use 1000 rows)

To avoid an error being returned on unused rows, the formula in B2 could be amended
=IFERROR(INDEX(Sheet2!A:A,MATCH(Sheet1!$A2,Sheet2!$C:$C,0)),"")
 
Last edited:
Upvote 0
Thank you mikerickson, this work great. There is an edge case I did not account for. In sheet2 some people only have First Names and no Last Names

When this happens the output in sheet1 for these people have no output, but they need for the First Name to be added to Sheet1
...

In my testing Tim was carried over to sheet1.
 
Upvote 0
One more small amendment

Code:
Set foundCell = rngData1.Find(what:=JoinedName, LookAt:=xlWhole)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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