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 in 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
No comments:
Post a Comment