Function to invert names in cells

Hi, I guess I'm a novice user. I have a guest list workbook with multiple sheets and what I have been told by my superior to invert the name in each cell so fro example: John Doe-->Doe, John.

As simple and elementary as this sounds I was baffled that I have never needed to do this before and couldn't find a function. I really don't want to manually change each cell (1700) Any help would be much appreciated!

Shouldn't be too hard, but before we can proceed, can you read the forum rules about thread titles and change appropriately. Rule #1

No problem, thanks for the update...

Assuming your names are in A1,

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

and then just copy down

Doesn't seem like you should be getting a circular reference.

Can you post the formula you are using and where are the names? A1, A2, A3 etc.

Originally Posted by reasonableman

The formula result is correct and lists the desired result,the error only occurs when I select OK.
The formula I have been using is such: =MID(A1,FIND(" ",A1, 1)+1,LEN(A1)) & ", " & LEFT(A1,FIND(" ",A1))

The names for the "practice" sheet are from A1 to A7. I am inputting the formula into A1 with the intention of then applying it to the remainder of the column. Each cell has a different name, but all in the same column. The error seems to be the original A1[=MID(A1...] that is according to Excel.

If the names are in A1 you can't then apply the formula to A1. That's why you get the circular reference.

If John Doe is in A1

apply the the above mentioned formula in B1 and you will get

Doe, John

If this continues to be a problem and the formula seems to not be cooperating, can you post the offending workbook?

Okay well after doing what you said, applying the formula to B1 the circular reference no longer occurred. Also, I did the evaluate formula and it worked through successfully to the intended result, however, after hitting enter nothing appeared in B1 except the formula. If you get it to work, then you can just apply to one sheet and let me know, dont worry about the whole workbook.

Thanks so much.

On the Department of State tab, I inserted a column between column B and D

In the new column C, I placed this formula and no problem.

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

Tina Kaidanow >> turned into >> Kaidanow, Tina

I cannot replicate what you are referring too.

Is it somewhere in this workbook? Also, you said nothing appeared after hitting enter but the formula.

Often times you can just format the cell as General and then try

Ctrl + H
Find what: =
Replace with: =

I would also suggest you remove these spreadsheet with all those title, names, e-mails. I don't think I would want my info out there like this.