second name first but want to change

[Matkalg]

hi

In cell A1 to A10 my names are in second name first then a space then the first names last but i want thier first names at the front and thier last names in another cell like b1 to b10

can anyone help

Matt

 

[Oaktree]

First name =RIGHT(A1,SEARCH(" ",A1)-2)
Last name =LEFT(A1,SEARCH(" ",A1)-1)

But, realize that names such as John von Neumann and Mary Jane Smith will be the Achilles heel to this "find the space" approach.

 

[Pekkavee]

Use Data - Text to columns.

Then you can use CONCATENATE formula in Column C

like
= CONCATENATE(B1," ",A1)

Pekka

 

[babycody]

How about

Book6
	A	B	C	D
1	Smith Mike	Mike Smith
2	James Frank	Frank James
3	Little Susie	Susie Little
4	Williams Patrick	Patrick Williams
5
Sheet1

Does that work for you? This has the same limitations that Oaktree stated with spaces.

 

[lenze]

Select the cells and choose Data>text to Column. Check delimited and hit next. Choose space for the delimiter. Hit next, Choose Finish. The first names will be moved to column B. Cut Column A and Copy to Column C. Now in A1, Enter

=B1&" "&C1

Copy down and delete columns B and C

HTH

lenze

 

[babycody]

Here is a fix for the space problem. Hope this helps. The Marry Jane Smith or Smith Marry Jane problem would still apply.

Book6
	A	B	C	D
1	Smith Mike	Mike Smith
2	James Frank	Frank James
3	Little Susie	Susie Little
4	Von Williams Patrick	Patrick Von Williams
5	Daniels C. Peter	Peter Daniels C.
6
Sheet1

 

[Matkalg]

what about changing the names around in the same cell to still end up in the same cell but round the right way

Thanks for all your help

 

[HalfAce]

what about changing the names around in the same cell to still end up in the same cell but round the right way

Insert a new column B.
Use this formula (or pretty much any of the others offered above) in B1 & copy down.

=MID(A1,FIND(" ",A1,1)+1,LEN(A1))&" "& LEFT(A1,FIND(" ",A1,1)-1)

Delete column A and your new column B becomes column A.
Copy column A and PasteSpecial as Values.

Note, this still has the same limitations with 3 word names as mentioned above, but it looks like babycody might have that handled.

Hope it helps.

 

[Matkalg]

last one i prom, can i make cell a1 (first name) and cell b1 (last name) go into one cell as a first then last name

Thanks

from THE THICK 1

 

[babycody]

=A1&" "&B1