Separating names in Excel

Separating names in Excel where there are no spaces

A recent question from a client involved a common request of separating names in Excel. In this case however, there was the added complexity that all spaces had been accidentally removed so there was no obvious way to split the name and surname.

One key consistency was that the start of the names and surnames were in capital letter e.g. AdrianMiric. How can we split the names based on whether the letter is upper or lower case?

The key to this solution is understanding the CODE function in Excel which specifies the numeric code that the computer uses to identify the specific character. If you type =CODE(“A”) into Excel it gives you 65 and if you use =CODE(“a”) you get 97. So any codes greater than 96 are lower case letters. You can now use this to determine where the capital letters are.

You can use the MID function to separate your name into a letter per cell and then with a combination of IF and CODE functions you can determine where the surname begins. Once you have that it is a simple process to split the cells (again using the MID function).