Occasions when a persons first name and surname appear in the same cell in Excel may call for a need to ‘split’ the data apart in order to store in a more structured way, in 2 separate columns. Excel can accomplish this quite easily with the Split function in VBA, but you must also be aware that if the cell contains the title such as Mr/Mrs etc, the split may not behave as expected and return the title as the first name. We have a simple script that removes the most common titles if they are detected at the beginning of the data in a cell. We also have a UDF to extract first word or last word from a cell that can be used as a formula or within VBA.
The following code works in a way that determines where the first space is when reading normally from left to right. When it finds the space it determines that by grabbing the first character up until the last one before the space was detected, that will be the first name. The same principal happens for the last name but in reverse, going from right of the text to left looking for the first occurrence of a space.
Various additional functions could be incorporated into the code to ‘grab’ a middlename for instance or ignore the usual set of titles such as Mr, Mrs with or without a space afterwards. Depending upon the structure of your data, you may need to adjust the example code below. Add a loop or a for-next process to manage lots of data in a column/row.
As always, feel free to drop us a message to assist with your project, this set of examples or leave a comment below.
‘Get first name
first = Split(ActiveSheet.Range(“C1″), ” “)(0)
‘Get last name
findlast = Split(ActiveSheet.Range(“C1″), ” “)
last = findlast(UBound(findlast))