Exploded Name using MS-Excel

This tutorial is now complete. If you find any errors, please report it to Divinderjit Singh Chattrath.

Separating the first name, middle name and last name into separate cells 

 

This application was developed using MS Excel Functions.

Let us first set background colour of some cells as shown in the diagram below. Also type down some labels as "enter your name:" and "Number of words="

Our goal is to separate the parts of name that has been entered by the user in the yellow cell into three different cells. The user can delete everything in the yellow cell and leave it blank. They can enter one word, two words or three word names.  The application should be able to count how many words are entered. 

Let us find the number of words entered in C18 (yellow cell) and present it in front of the label "Number of words=". For this you have to enter the following formula in the cell D19 where it shows 0 at this time.

=IF(C18="",0,1)

This will show "0" in cell D19 if there is nothing in cell C18 (Yellow Cell).

Now enter "a b c" in cell C18 (yellow cell). This will show "1" in cell D19

Our next Goal is to...

... replace "1" in the above formula with the expression that will calculate words. Let us develop that expression now.

For this we will first find the length of the string entered in cell C18 (yellow cell) by using the following formula

LEN(C18)

Our entered expression"a b c" is of 5 characters length

We can remove all spaces by substituting the spaces with nothing using the following formula

SUBSTITUTE(C18," ","")

Then we find the length of the string without the spaces

LEN(SUBSTITUTE(C18," ",""))

This will return 3 as two spaces are removed and only three characters are left 

Now we can subtract these two expression

LEN(C18)-LEN(SUBSTITUTE(C18," ",""))

this will return 5-3 = 2 and so if you add 1 to it, you will get the right amount of words entered

LEN(C18)-LEN(SUBSTITUTE(C18," ",""))+1

The whole equation now looks like this

=IF(C18="",0,LEN(C18)-LEN(SUBSTITUTE(C18," ",""))+1)

 

CASE 1: Nothing is entered in yellow box

 

CASE 2: One word is entered in yellow box

 

CASE 3: Two words are entered in yellow box

 

CASE 4: Three words are entered in yellow box

 

Let us achieve this by first working on the green boxes

GREEN BOXES

The first green box

(Cell C20) is either nothing or "First name=".

This is easy to achieve

=IF(D19=0,"","First Name=")

 

The second green box 

(Cell C21) is either empty or "Middle name=" or "Last name=" depending on the user entry.

Let us develop this from the code of the first green box. Replace "First Name=" with another if statement

=IF(D19=0,"","First Name=")

=IF(D19=0,"",IF(D19=1,"","Last Name="))

Replace  "Last Name=" with another if statement

=IF(D19=0,"",IF(D19=1,"",IF(D19=2,"Last Name=","Middle Name=")))

so the final equation is

=IF(D19=0,"",IF(D19=1,"",IF(D19=2,"Last Name=","Middle Name=")))

 

The last green box 

(Cell C22) is either empty or "Last name="

Let us develop this from the code of the first green box. Replace "First Name=" with another if statement

=IF(D19=0,"","First Name=")

=IF(D19=0,"",IF(D19=1,"","Last Name="))

Replace  "Last Name=" with another if statement

=IF(D19=0,"",IF(D19=1,"",IF(D19=2,"","Last Name=")))

So the final equation is

=IF(D19=0,"",IF(D19=1,"",IF(D19=2,"","Last Name=")))

 

Now let us work on blue boxes. These will check the contents in corresponding green boxes.

BLUE BOXES

The first blue box

should have nothing or first name

So the logic is 

=IF(C20="","","Something") 

This will display "Something" only if C20 is not empty

Now we need to replace "Something" with the first name. Let us develop that first...

If there is only one word in cell C18 then we need to just display it (because it is the first name)

=IF(C20="","",IF(D19=1,C18,"SubPart")) 

But what if there are two words in C18 (yellow cell). In this situation we will have to extract the first name. For this we need to replace "Subpart" with another expression. This expression will find the location of the first space and then extract the part of string from the first character to one less than the location of first space.

=IF(C20="","",IF(D19=1,C18,MID(C18,1,SEARCH(" ",C18,1)-1))) 

So the final equation is

=IF(C20="","",IF(D19=1,C18,MID(C18,1,SEARCH(" ",C18,1)-1))) 

 

The second blue box

should have nothing or last name or middle name

The logic is to check cell C21 (corresponding green cell), and if the cell is empty then nothing is displayed or "Something" is displayed.

=IF(C21="","","Something")

This will display "Something" only if C21 is not empty

Now we need to replace "Something" with the last/middle name if there are only two/three words, respectively, in cell C18 (yellow cell). Let us develop it. First we will replace "Something" with another if statement

=IF(C21="","","Something")

=IF(C21="","",IF(D19=2,"LastPart","MiddlePart")) 

The "LastPart" is displayed if the number in cell D19 is exactly equal to two. So we search for the location of the first space. Using the "MID" and "SEARCH" functions we extract the part of the string from the location of first space +1, till the end of the string.

MID function works like ...

MID(Name, start_location, Number_of_characters_to_extract)

MID(C18, location_of_first_space + 1, Number_of_characters_in_the_second_word_of_the_Name_String)

Let us first find "location_of_first_space", For that we will need to use the SEARCH function to find the location of the first space. The SEARCH function works like this:

SEARCH(Searching for, searching in, starting from location)

SEARCH(Space, Name, Beginning_location_of_Name_String)

SEARCH(" ", C18, 1)

This will give us the location of first space. We are trying to extract the the word that follows the first space hence we need to add one to it 

MID(C18 ,SEARCH(" ",C18,1)+1, Number_of_characters_in_the_second_word_of_the_Name_String)

Let us now develop "Number_of_characters_in_the_second_word_of_the_Name_String". Logic for this is that we will find the length of the whole string and then from that we will subtract the number of characters up to first space's location

LEN(C18)

...will find the length of the whole string

for example if the name string is Divinderjit Singh, then 11+1+5 = 17 will be returned

SEARCH(" ",C18,1)

...will find the location of the first space

for example if the name string is Divinderjit Singh, then 12 will be returned

LEN(C18) - SEARCH(" ",C18,1)

for example if the name string is Divinderjit Singh, then 17 - 12  = 5 will be returned. That is the number of characters in the last name.

Let us insert this expression.

MID(C18 ,SEARCH(" ",C18,1)+1, Number_of_characters_in_the_second_word_of_the_Name_String)

MID(C18 ,SEARCH(" ",C18,1)+1, LEN(C18)-SEARCH(" ",C18,1))

 

Let us use this MID function to develop our formula

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),"MiddlePart")) 

 

So till now we have developed the formula to handle the name with two words. If we have three words in the name then the "MiddlePart" is displayed.

Now we will replace the "MiddlePart" with some code so that we can display the middle name.

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),"MiddlePart")) 

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),"Part_between_two_spaces")) 

 The logic to develop the formula for the middle part is that we need to extract the part between two spaces.

 So let us deal with "Part_between_two_spaces". We will use MID function again

 

MID function works like ...

MID(Name, start_location, Number_of_characters_to_extract)

MID(C18, location_of_first_space + 1, Number_of_characters_in_the_second_word_of_the_Name_String)

Let us first find "location_of_first_space", For that we will need to use the SEARCH function to find the location of the first space. The SEARCH function works like this:

SEARCH(Searching for, searching in, starting from location)

SEARCH(Space, Name, Beginning_location_of_Name_String)

SEARCH(" ", C18, 1)

This will give us the location of first space. We are trying to extract the the word that follows the first space hence we need to add one to it 

MID(C18 ,SEARCH(" ",C18,1)+1, Number_of_characters_in_the_second_word_of_the_Name_String)

Let us now develop logic for finding "Number_of_characters_in_the_second_word_of_the_Name_String". Logic for this is that we will find the location of the first space, increase that by one and subtract it from location of second space

 

For example in the name string

Divinderjit Singh Chattrath

first space is at 12. Increasing that by one gives 13 and second space is at 18

18 - (12+1) = 5

the name string do have five characters in the middle name so we are good to go ahead

but now the problem is how to find the location of the second space, Well the trick is ...

...to find the         location of first space         from           ( the location of the first space +1)

...to find the         location of first space         from           (12+1)

...to find the        location of  first space         from           13

SEARCH(" ", C18, start from 13)

SEARCH(" ", C18, start from 13)

SEARCH(" ", C18, start from 12 + 1)

SEARCH(" ", C18, SEARCH(" ", C18,1) + 1)

...which is 18

we will use this expression soon...

 

now we will use MID function to extract the middle name from the name string is Divinderjit Singh Chattrath which is in C18 (yellow cell)

MID(Name, start_location, Number_of_characters_to_extract)

MID(C18, 13, 18 - 13 )

MID(C18, SEARCH(" ", C18, 1)+1, 18 - SEARCH(" ", C18, 1)+1)

 

now let us substitute the expression that we have developed for "18"

MID(C18, SEARCH(" ", C18, 1)+1, 18 - SEARCH(" ", C18, 1)+1)

MID(C18, SEARCH(" ", C18, 1)+1, SEARCH(" ",C18,SEARCH(" ",C18,1)+1)- SEARCH(" ", C18, 1)+1)

extract the number of characters of middle name from that place. The problem is that we will have to find the number of characters of the middle name also! 

 

LEN(C18)

...will find the length of the whole string

for example if the name string is Divinderjit Singh Chattrath, then 11+1+5+1+9 = 27 will be returned

SEARCH(" ",C18,1)

...will find the location of the first space

for example if the name string is Divinderjit Singh Chattrath, then 12 will be returned

 

LEN(C18) - SEARCH(" ",C18,1)

for example if the name string is Divinderjit Singh Chattrath, then 17 - 12  = 5 will be returned. That is the number of characters in the last name.

 

Let us insert this expression.

MID(C18 ,SEARCH(" ",C18,1)+1, Number_of_characters_in_the_second_word_of_the_Name_String)

MID(C18 ,SEARCH(" ",C18,1)+1, SEARCH(" ",C18,SEARCH(" ",C18,1)+1)-1-SEARCH(" ",C18,1)+1)

 

So the final expression to find the middle name when we have 3 words is as follows...

MID(C18,SEARCH(" ",C18,1)+1,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)-1-SEARCH(" ",C18,1)+1)

Let us use this MID function to develop our current formula, which is...

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),"MiddlePart")) 

where "Middle Part will be replaced by the sxpression that we just built. So which becomes...

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),MID(C18,SEARCH(" ",C18,1)+1,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)-1-SEARCH(" ",C18,1)+1))) 

 

So the final equation is

=IF(C21="","",IF(D19=2,MID(C18,SEARCH(" ",C18,1)+1,LEN(C18)-SEARCH(" ",C18,1)+1),MID(C18,SEARCH(" ",C18,1)+1,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)-1-SEARCH(" ",C18,1)+1))) 

 

The third blue box

should have nothing or last name

=IF(C22="","","LastName")

So let us develop logic for the "LastName". Find the second space and from there extract the number of characters till the end.

We know how to find the location of second space. This we learnt in the section called "The second blue box".

SEARCH(" ",C18,SEARCH(" ",C18,1)+1)

this will return 18 for the name string Divinderjit Singh Chattrath

 

Now if we subtract this from length of the whole Name string, we will end up with the length of the last name. Cool!

LEN(C18) - SEARCH(" ",C18,SEARCH(" ",C18,1)+1)

this will return 27 - 18 = 9 for the name string Divinderjit Singh Chattrath, which is the number of characters in last name

 

let us use this to build the required expression using MID function

MID(C18, Start location, number of characters to extract)

MID(C18, Start location, LEN(C18) - SEARCH(" ",C18,SEARCH(" ",C18,1)+1))

 

Now let us build expression for the "Start location"

The location of second space if increased by one gives us the location to start extracting the last name as the want to extract from the character that is next to second space.

SEARCH(" ",C18,SEARCH(" ",C18,1)+1)+1

 

this will return 19 for the name string Divinderjit Singh Chattrath

 

Let us use this to develop our current expression, which is...

MID(C18, Start location, LEN(C18) - SEARCH(" ",C18,SEARCH(" ",C18,1)+1))

MID(C18, Start location, LEN(C18) - SEARCH(" ",C18,SEARCH(" ",C18,1)+1))

MID(C18, SEARCH(" ",C18,SEARCH(" ",C18,1)+1)+1, LEN(C18)-SEARCH(" ",C18,SEARCH(" ",C18,1)+1))

 

MID(C18,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)+1,LEN(C18)-SEARCH(" ",C18,SEARCH(" ",C18,1)+1))

 

So out final expression

=IF(C22="","","LastName")

becomes

=IF(C22="","",MID(C18,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)+1,LEN(C18)-SEARCH(" ",C18,SEARCH(" ",C18,1)+1)))

 

So the final expression in cell D22 is...

=IF(C22="","",MID(C18,SEARCH(" ",C18,SEARCH(" ",C18,1)+1)+1,LEN(C18)-SEARCH(" ",C18,SEARCH(" ",C18,1)+1)))