# second name first but want to change

#### Matkalg

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

Use Data - Text to columns.

Then you can use CONCATENATE formula in Column C

like
= CONCATENATE(B1," ",A1)

Pekka

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

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

Matkalg said:
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.
Code:
``=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.

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

=A1&" "&B1

Replies
8
Views
253
Replies
5
Views
831
Replies
3
Views
175
Replies
12
Views
230
Replies
4
Views
165

1,196,322
Messages
6,014,636
Members
441,832
Latest member
tony tessman

### 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.

### Which adblocker are you using?

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

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