This is silly, but I need a formula to replace the first letter in a name with the last letter. Example: John Smith --> Sohn Smitj. None of the names have middle initials.
Thank you!!
This is silly, but I need a formula to replace the first letter in a name with the last letter. Example: John Smith --> Sohn Smitj. None of the names have middle initials.
Thank you!!
Suppose the name is in A1. You can use this formula:
Formula:=IF(LEN(A1)<3,IF(A1="","",IF(LEN(A1)=2,PROPER(RIGHT(A1,1)&LEFT(A1,1)),A1)),UPPER(RIGHT(A1,1))&MID(A1,2,LEN(A1)-2)&LOWER(LEFT(A1,1)))
This gives Hohn Smitj, not Sohn Smitj. I'm not sure where the S came from in your example.
Last edited by SlipEternal; 07-03-2017 at 03:32 PM.
Looking at your description rather than the example (which does not match the description), I came up with =PROPER(SUBSTITUTE(A2,LEFT(A2,1),RIGHT(A2,1))).
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks