You have received an Excel spreadsheet containing a few hundred names in a single column.
You want the surnames in one column and first names in another so that when you churn out letters, for instance, the separate columns offer the option of addressing the recipient by his first name or surname.
Also, the names in the list are likely to be all in upper case.
As you do not want to come across as shouting at the recipient, you want only the first letter of each word to be capitalised.
Okay then.
Say the first name in the list is in cell A2.
In the cell on the right, B2, type in this formula and hit the Enter key: =PROPER(A2)
This will convert LIM KOH PEH into Lim Koh Peh in cell B2.
To extract the surname - assuming it is the first word that appears in the name - type this into the cell C2 which is on the right of B2: =LEFT(B2,FIND(" ",B2)-1)
"Lim" will appear in cell C2.
To get the first name into D2, type this into that cell: =MID(B2,FIND(" ",B2,1) +1,LEN(B2))
You will find "Koh Peh" in D2.
Once the formulae are done, you can use them to convert hundreds or thousands of names in seconds - instead of fussing over each name manually.
Just copy the formulae into the columns for the rest of the names on the list.
Select B2, click and drag the cursor to D2.
Then, right-click and choose Copy.
Now, select B3, drag the cursor to D3 and then drag it straight down all the way until you reach the row with the final name on the list.
Then, right-click and choose Paste.
All done in less than five minutes.
This story was first published in The Straits Times Digital Life.
