Sponsored Link

Sponsored Link

Friday, October 24, 2008

Reverse the two words in MS Excel

Here we want to change Shivani Kumar to Kumar Shivani. It can be very useful when you want to change the data of First Name Last Name to Last Name First or vice versa.

Now lets take look at few basic ideas about develop

  1. Right(text, number of characters) function returns number of character of text starting from right. Example: Right(“Shivani Kumar”, 5) will return Kumar as a result
  2. Left(Text, number of characters) function returns number of characters of text starting from left. Example: Left(“Shivani Kumar”, 7) will return Shivani as a result
  3. Length on any text can be determine using LEN(text) function.Example : Len(“Shivani Kumar”) will return 13 as a result.
  4. Now the challenge is to find position of space in the text which can be done using Find(what to find, from where) function. Example : Find(“ “, “Shivani Kumar”) will return 8.

Now, we will combine the entire all the formulas to return reverse of two words.

MS Excel, reverse text trick

Formula: =RIGHT(A3,LEN(A3)-FIND(" ",A3))&" "&LEFT(A3,FIND(" ",A3)-1)

Here, we have learned and used three function viz LEFT(), RIGHT(), FIND(), LEN() which are basic. However, combining all together is an advance step.

We assure you knowledge, not SPAM!

2 comments:

Vinu1smile said...

HI Ahamed,

I have a question regarding reverse the word in two text:

Why do we use at the end -1, i got the same answer without this. Whats the importance of -1. Please explain me.

Regards,
vinu.

Nick said...

Great example!

Post a Comment