This guide will show you some quick and easy steps to removing the first word from a string in Excel. This is perfect for that situation where you have name salutations to remove from a data field, for example where you want Mr John Smith to become John Smith…
Remove First Word from String
You can use Excel formula to manipulate data in pretty much anyway you can imagine, sometimes you may need to use more than one function to achieve the result but in Excel most things as possible and removing the first word from a string is a straight-forward task.
The most common reason for removing the first word from a string in Excel is to remove name salutations, for example the data may say ‘Mr John Smith’ and you would like it to say ‘John Smith’ and remove the ‘Mr’ part:
If your data starts in cell A2 and you want to put the new version of the name in the next column then in Cell B2 you would type the following formula (you can copy and paste the formula including the equals sign into cell B2 if you prefer):
This formula can be dragged down all corresponding cells to remove the first word from the string, in this case remove the name salutation:
Taking a stage further
Taking the data manipulation a stage further it is possible to fully separate the name into individual parts, this can be useful when you have imported data from other sources, particularly text formats and now want to break the data back out for analysis or record-keeping.
The formula’s to use for splitting out the name into parts is detailed below, all the formula’s refer to your data as being in cell A2. If your data is not in cell A2 then amend all occurrences of this in the formulas:
Return the Salutation: =LEFT(A2,SEARCH(” “,A2)-1)
Return the first name: =MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)
Return the surname: =IF(ISERR(FIND(” “,A2)),””,RIGHT(A2,LEN(A2)-FIND(“*”,SUBSTITUTE(A2,” “,”*”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))))
If your data is formatted in the same manner as the example then all the formulas will work but it is possible that your data could be formatted slightly differently. If this is the case they do not fear, there is always a work-around or a different way to write the above formulas to achieve the desired result.
I have chosen not to go into detail on the logic behind the formulas in the post as I have covered them in detail in a previous post on text string manipulation. For a deeper understanding I would encourage you to read that guide as it will allow you to create customised formula’s that are specific to your needs.