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.

Sony Xperia Z5 review: Hands-on with the phone which the Z3+ should have been

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

Get your photos turned into illos by some of the world’s best artists

What to expect at the September Apple event: Apple announces 9 September event, sends out invites