Are you a Google Spreadsheets users? Take a look at this tips to invert words into a string.
Ok guys, finally I have a litte bit of time to add a new post on my blog. Today I want to return to talk about Google Spreadsheets, illustrating simple tips very useful to invert automatically (using some basic formula) the order of words in a cell.
You can also use all formulas in this example on Excel.
Take a look at the spreadsheet here Download Excel fileStep 1 - Input data
For example, image you have a column with several names (name + surname):
...and for some reason you need to invert the order in surname + name. You can do it manually... but in case of more then ten names could spend a lot of time to do it. A good solution is using some formulas
Step 2 - Find a criteria to separate words
What is it the criteria you can use to split single words? In this example is the space (" ") between name and surname. You have only to find the "position" of the space (" ") to have a reference to split the content in the cell. You can do it using find() formula in this way:
This forumlas return the index (position) of the space (" "), contained into the text in the cell B5, starting to the position "1".
...for the cell B5 (Jack Bauer) the space is at position 5 (index = 5).
Step 3 - Extract words
Now, in a new column you can extract the surname and in another column the name. To extract the surname you can use right() formula combined with len() formula:
...where len() formula return the lenght (number of chars) of the text contained in the cell B5.
To extract the name you can use left() forumla:
Step 4 - Concatenate extracted words in a new order
Finally, you can concatenate words you extracted in the order "surname and name", using concatenate() forumla:
...concatenate surname (D5), a space (" ") and name (E5).
Jack Bauer
Christian Troy
Gregory House
Micheal Scofield
Lincoln Burrows
...
...and for some reason you need to invert the order in surname + name. You can do it manually... but in case of more then ten names could spend a lot of time to do it. A good solution is using some formulas
Step 2 - Find a criteria to separate words
What is it the criteria you can use to split single words? In this example is the space (" ") between name and surname. You have only to find the "position" of the space (" ") to have a reference to split the content in the cell. You can do it using find() formula in this way:
=find(" ";B5;1)
This forumlas return the index (position) of the space (" "), contained into the text in the cell B5, starting to the position "1".
...for the cell B5 (Jack Bauer) the space is at position 5 (index = 5).
Step 3 - Extract words
Now, in a new column you can extract the surname and in another column the name. To extract the surname you can use right() formula combined with len() formula:
=right(B5;len(B5)-C5)
...where len() formula return the lenght (number of chars) of the text contained in the cell B5.
To extract the name you can use left() forumla:
=left(B5;C5)
Step 4 - Concatenate extracted words in a new order
Finally, you can concatenate words you extracted in the order "surname and name", using concatenate() forumla:
=concatenate(D5," ",E5)
...concatenate surname (D5), a space (" ") and name (E5).
It's all!
Take a mind, you can also use the same formulas in Excel but remember only to separate formula parameters using ";" instead of ",".
Take a mind, you can also use the same formulas in Excel but remember only to separate formula parameters using ";" instead of ",".
Related Content
See also:
- Google Spreadsheets: formulas tutorial
- Gantt Chart with EditGrid online spreadsheets
- Implement a Project Plan and manage activities with Google Spreadsheets
- Project Management: a project plan with Excel (template)
- Gantt Chart using Google Spreadsheets and conditional formatting
- Project Management: Excel Gantt Chart Template
I am Antonio Lupetti, Engineer, Pro Blogger, Mac user, Web addicted.
Rome, IT.
Sponsored Links
Share this post
Old Comments
Good To See You Back
24 - Nip/Tuck - House MD - Prison Break
Good choices :)
@jaswinder: thanks! :)
@fede777: I like this series! :)))
Could you also write the entire formula in one cell with the only variable being the original cell?
=concatenate(right(B5;len(B5)-find(" ";B5;1))," ",left(B5;find(" ";B5;1)))
@arthur: yes, you can do that using a sngle cell. I divided cells to be clearer.
It's great to see you're back, Antonio :)
I've only recently begun using Excel (despite having this on my computers for years) and this is certainly a useful trick to use!
Thanks for posting this,
Amanda
Cool! Very interesting.
wow blog yang keren
This very nice, Good job
regards
web design company