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]

Hello,

I have a column of names on sheet1

Names
Tim Hanson
Bob Jones-Kelty
Hank Edward Williams

On sheet2 I have two columns First Name and Last Name in columns A & B

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

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

 

[mikerickson]

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?

 

[mikerickson]

This should do what you want, you might want to add some Trim's

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

 

[Yongle]

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

	A​	B​	C​	D​
1​	Names	First	Second	Formula in B2
2​	Tim Hanson	Tim	Hanson	=INDEX(Sheet2!A:A,MATCH(Sheet1!$A2,Sheet2!$C:$C,0))
3​	Bob Jones-Kelty	Bob	Jones-Kelty
4​	Hank Edward Williams	Hank	Edward Williams
5​
6​

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​	First Name	Last Name	Helper	formula in column C​
2​	Tim	Hanson	Tim Hanson	=A2&" "&B2​
3​	Bob	Jones-Kelty	Bob Jones-Kelty	=A3&" "&B3​
4​	Betty	Anderson	Betty Anderson	=A4&" "&B4​
5​	Hank	Edward Williams	Hank Edward Williams	=A5&" "&B5​
6​

Sheet: Sheet2

 

[capson]

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

First Name      Last Name
Tim

Thanks

 

[capson]

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

 

[Yongle]

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)),"")

 

[mikerickson]

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.

 

[mikerickson]

One more small amendment

Set foundCell = rngData1.Find(what:=JoinedName, LookAt:=xlWhole)