Swap Firstname & Lastname

  jaraba 21:45 PM 22 Mar 12
Locked
Answered

I have spreadsheet which has names in one column as "Smith John" and I want to transfer them to another 2 columns with Firstname in one & lastname in the other.

Any advice on a formula to do this would be much appreciated.

I know I can do this by using text to columns but the info is pasted in from another program and I need it to be swaped automatically.

  Fruit Bat /\0/\ 22:40 PM 22 Mar 12
Answer

Right(text, number of characters) function returns number of character of text starting from right. Example: Right(“Blogs Fred”, 4) will return Blogs as a result

Left(Text, number of characters) function returns number of characters of text starting from left. Example: Left(“Blogs Fred”, 5) will return Fred as a result

Length on any text can be determine using LEN(text) function.Example : Len(“Blogs Fred”) will return 10 as a result.

To find position of space in the text which can be done using Find(what to find, from where) function. Example : Find(“ “, ““Blogs Fred””) will return 6.

Formula: =RIGHT(A3,LEN(A3)-FIND(" ",A3))&" "&LEFT(A3,FIND(" ",A3)-1) will reverse the two words in A3

  jaraba 07:47 AM 23 Mar 12

*Fruit Bat /\0/*

Many thanks

Will mark as resolved.

Advertisement

This thread is now locked and can not be replied to.

How to get Windows 10 now: how to download and install Windows 10 even if GWX.exe is missing

1995-2015: How technology has changed the world in 20 years

Nokia rolls out spherical camera for virtual reality apps

7 best iPhone web browser apps: Why Safari for iOS is still a better option than Chrome, Dolphin,…