Moving the last name into a different column

[dankar]

I need help with the following, I have a list of 200+ name and I want to cut the last name and paste in the first column like the image.

I have 3 cases in all the names :
1. first name, middle name and last name ( I just want to remove the last name and paste it under last name column like first example in image)
2. First name and last name ( just want to remove the last name and paste it under last name column like second example in image )
3. Only first name. (just keep it as is)

Thanks.

 

[Joe4]

Is it possible to have last name with two words, like "Von Trapp"?
If so, it is going to very difficult to tell if names with 3 or more words have 1 or 2 words in the last name.

Also, are you looking for a VBA solution?
Because formula solutions are going to be able to replace the data in the original column (column L), unless you were to do that manually.
Or, do you want to split the names from column L into two other columns (separate of column L)?

 

[dankar]

Is it possible to have last name with two words, like "Von Trapp"?
If so, it is going to very difficult to tell if names with 3 or more words have 1 or 2 words in the last name.

Also, are you looking for a VBA solution?
Because formula solutions are going to be able to replace the data in the original column (column L), unless you were to do that manually.
Or, do you want to split the names from column L into two other columns (separate of column L)?

No for the last name is always one word.
if it will be easier to split them into two other columns, its ok..i can do it in a separate sheet then copy the results to the main one
VBA would be ok also, I'm not familiar with it but i will copy the code into Excel and try it..thanks for the help

 

[JEC]

If your list of names starts in L1 and column K is empty then

Sub jec()
 ar = Sheets(1).Range("K1", Sheets(1).Cells(Rows.Count, 12).End(xlUp))
 
 For i = 2 To UBound(ar)
   ar(i, 1) = Split(ar(i, 2))(UBound(Split(ar(i, 2))))
   ar(i, 2) = Replace(ar(i, 2), " " & ar(i, 1), "")
 Next
 
 Sheets(1).Range("K1", Sheets(1).Cells(Rows.Count, 12).End(xlUp)) = ar
End Sub

 

[Sufiyan97]

With the given data Try

Book2
	A	B	C
1	Last Name	First Name	First Name Original
2	Murray	Chad Michael	Chad Michael Murray
3	Lawson	Ralph	Ralph Lawson
4		Steve	Steve
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=IF(ISERROR(FIND(" ",C2)),"",TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)))
B2:B4	B2	=REPLACE(C2,FIND(A2,C2),LEN(A2),"")

 

[dankar]

If your list of names starts in L1 and column K is empty then

Sub jec()
 ar = Sheets(1).Range("K1", Sheets(1).Cells(Rows.Count, 12).End(xlUp))
 
 For i = 2 To UBound(ar)
   ar(i, 1) = Split(ar(i, 2))(UBound(Split(ar(i, 2))))
   ar(i, 2) = Replace(ar(i, 2), " " & ar(i, 1), "")
 Next
 
 Sheets(1).Range("K1", Sheets(1).Cells(Rows.Count, 12).End(xlUp)) = ar
End Sub

Hi,

the list starts in L2 and column K is empty.

The list of names is over 250 name.

i deleted the first row of my to list (as its the header) so the list of names starts in L1 but this is what I got:

 

[dankar]

With the given data Try

Book2
	A	B	C
1	Last Name	First Name	First Name Original
2	Murray	Chad Michael	Chad Michael Murray
3	Lawson	Ralph	Ralph Lawson
4		Steve	Steve
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=IF(ISERROR(FIND(" ",C2)),"",TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99)))
B2:B4	B2	=REPLACE(C2,FIND(A2,C2),LEN(A2),"")

This one worked perfect, just the firs name in Column B came with a "space at the end of the name" how can I omit the space after each name, even if you will add another separate column its ok with me..thanks again

 

[Sufiyan97]

Try

Moving the last name into a different column.xlsx
	A	B	C
1	Last Name	First Name	First Name Original
2	Murray	Chad Michael	Chad Michael Murray
3	Lawson	Ralph	Ralph Lawson
4		Steve	Steve
Sheet3

Cell Formulas
Range	Formula
A2:A4	A2	=TRIM(IF(ISERROR(FIND(" ",C2)),"",TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",99)),99))))
B2:B4	B2	=TRIM(REPLACE(C2,FIND(A2,C2),LEN(A2),""))

 

[JEC]

You may have to change your sheet name in the code.

 

[dankar]

You may have to change your sheet name in the code.

the sheet name is sheet1, shall i change Sheets(1) to sheet1, i tried but still not working.