Swap Firstname & Lastname

  jaraba 21:45 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 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 23 Mar 12

*Fruit Bat /\0/*

Many thanks

Will mark as resolved.

Advertisement

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

UK's best mobile network 2016: best 3G, best 4G, best mobile networks revealed

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

Adobe Animate replaces Flash Pro

23 tips & tricks for Mac OS X El Capitan: The best, coolest and least well-known new features in OS1…