Last name first on ios

[Msmithr99]

Hello

Using office 365 wanting to convert John Smith to Smith John

[BadlySpelledBuoy]

Assuming John Smith is in A1, this in any other cell will reverse the names:

=RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

BSB

[Sam Capricci]

in the same cell? for the same cell you could use =RIGHT(A2,FIND(" ",A2)*1)&" "&LEFT(A2,FIND(" ",A2)-1)
Do you have others with middle initials?

[Msmithr99]

Assuming John Smith is in A1, this in any other cell will reverse the names:

=RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

BSB

The result is #name?

[Excelski]

Works for me.......check you have entered the formula correctly, #name normally means typo in the formula

[GeoffW283]

I don't see the #name error either.
However I think there's an inaccuracy in both the above formulas. They happen to work with the particular first and last name lengths of "John Smith" but don't work with, for example: Jo Henderson
The following minor change fixes it I think:

Please Login or Register  to view this content.

[Msmithr99]

Space between “ “ or no space

[Msmithr99]

Works when I open new xls and don’t copy past original data

[Msmithr99]

Paste not past

[GeoffW283]

Looking at post #7 it looks like you have so-called smart quotes (sloping) rather than the required vertical quotes. That would cause a #name error. MS-word is fond of substituting smart quotes if you somehow used it in copy/pasting the formula.

[Msmithr99]

THANKYOU! Unbelievable.. I’ll be careful of that going forward. Makes sense that it didn’t work after copy paste. It probably carried “formatting” from global setting.

[GeoffW283]

Thanks for the feedback. If you’re all set then can you please mark the thread as solved? To do this select Thread Tools from the menu link above your first post and mark this thread as SOLVED. Thanks!

[FlameRetired]

Late to the party.

Just for fun.

Please Login or Register  to view this content.

[Msmithr99]

Thank you to all responders! I added the len(a1)- and that indeed fixed the text length variability.

In order for me and others to learn this bit of coding, would one of you explain the commands used and how they tell excel data management. Or is there an "app for that?" *notice straight quotes*!

[AlKey]

Or this

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

[GeoffW283]

explain the commands used and how they tell excel data management.

I'll try to explain the post-6 formula, but first some general advice.

The first thing is to review the help for any individual functions that you are not familiar with. One convenient way to do this is to select the cell with the formula then click on "fx" by the formula bar. This will bring up a "Function Arguments" window. This window will update as you click on different functions in the formula bar and will give you a quick one line summary of what the function does and each of its arguments. You can get more detailed help on a function by clicking "Help on this function" at the bottom left of the "Function Arguments" window.

Once you understand the individual functions in a formula, the next step is to see how they work together to meet the requirements of the particular formula in question. Excel provides a took for this. Click on the cell with the formula to be examined and then on the ribbon go to Formulas > Evaluate formula. Here you can watch Excel evaluating the formula step by step. It's a great way to get insight into how a formula works.

Now to the specific formula in post #6.
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

The right() function returns a specified number of characters from the right end of the supplied text. It takes 2 arguments: (1) the text to examine and (2) the number of characters from the right of the text to be returned. In our case the intent here is to extract the last name. The first argument is A1, assuming that is where the full name is located. The second argument needs another couple of functions, len() and find(), to help calculate how many characters the last name has. find() returns the position of the space character between the first name and last name. len() returns the entire length of the text in A1. The difference between the two is the length of the last name that the right() function needs for its second argument.

The left() function works similarly to the above (but a bit more simply) to isolate the first name.

The "&" operator concatenates the text either side of it so that the final result is last name, a space character and then first name - the desired result.

That's it! Hopefully this helps.

The Gurus & moderators are having fun with this - I'll leave it to them to explain their own formulas

[Msmithr99]

to turn off smart quotes on iOS: settings, general, keyboard, turn off smart punctuation

[Msmithr99]

Thank you for the explanation!

[GeoffW283]

No problem - I hope it helped a little. Thanks for the feedback.