We can count the number of spaces between words. Let’s have a look at our first example. In cell A2, we have spaces between words and at the end of each line, so the case is simple. We can use the LEN function here that counts the length of text (Fig. 1).
=LEN(A2)
Fig. 1 LEN function
Now, that we have the number of signs, we can remove spaces. We can use the SUBSTITUTE function here. This function will change the old text, which in our case is a space into new text. And since we want to remove spaces, our new text will be an empty text string written in double quotes (Fig. 2).
=SUBSTITUTE(A2,” “,“”)
Fig. 2 SUBSTITUTE function
We can see that the function removed all spaces from our text (Fig. 3).
Fig. 3 No spaces
Now, we can count the length of the text without spaces using the LEN function (Fig.4).
=LEN(SUBSTITUTE(A2,” “,“”)
Fig. 4 Counting the text length
Now we know the length (Fig. 5).
Fig. 5 Text length
Since we know how many spaces there are, we can use the formula to count the words. We just have to subtract this formula from the previous one (Fig. 6).
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))
Fig. 6 Subtracting formulas
And we have the number of spaces (Fig. 7).
Fig. 7 Number of spaces
We still have to remember that to count the number of words, we have to add 1 because there is only one space between two words (Fig. 8).
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))+1
Fig. 8 One space between two words
Now we know the number of words in each cell.
Fig. 9 Number of words
If we want to count words from all cells, we can use almost the same formula. The only thing we have to change is the range which now will be from A2 to A4 (Fig. 10).
=LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1
Fig. 10 Adding a new range
In Dynamic Array Excel, the results will be spilled, however we can sum the up (Fig. 11).
Fig. 11 Split results
In the previous version of Excel we can use the the SUMPRODUCT function (Fig. 12).
Today, we are going to talk about extracting first names, middle names and last names from full names. We define the first name as the first word from the left, the last name as the first word from the right, and the middle name, as everything between those two. Middle names are a bit complicated, as they can may consist of one word, a space, an abbreviation, or two or more words (Fig. 1)
Extract First Last and Middle Name with FormulasFig. 1 A list of names
Let’s start with extracting the first name. We just have to use the LEFT function, which extracts signs from the left. We have to insert the FIND function there, so that it finds the first space, and write the space in double quotes (Fig. 2)
=LEFT(A2,FIND(“ “,A2))
Fig. 2 Extracting the first name
Now, we have the first name (Fig. 3).
Fig. 3 The first name
But, we have to remember that the FIND function will return the position of the sign we’re looking for, which means that it finds the 7th position, which is a space (Fig. 4).
Fig. 4 7th position
The first name , however, consists of only 6 letters, which is what we want. It means that we want to extract less letters. So, we have write the formula once again, but this time we subtract one sign (Fig. 5)
=LEFT(A2,FIND(“ “,A2)-1)
Fig. 5 Subtracting one sign
Now, we have only the first name without unnecessary signs. After dragging it down, the whole column is full (Fig. 6)
Fig. 6 Full column
Let’s go to last names. This case is a bit more complicated. We don’t know whether we’re looking for the first, the second, the third or even a more distant space (Fig. 7)
Fig. 7 Too many possible spaces
There’s a trick to solve it. We have to repeat spaces. Within our text, we have to substitute a single space into many spaces, let’s say 29. We use the REPT function (Fig. 8)
=SUBSTITUTE(A2,” “,REPT(“ “,29))
Fig. 8 Repeating spaces
After accepting our function, we have our changed names with many spaces between them (Fig. 9)
Fig. 9 Names with many spaces
Now, we can start extracting names from the right side. We’re extracting not only all signs from our name, but also the remaining 29 signs (Fig. 10).
=RIGHT(SUBSTITUTE(A2,” “,REPT(“ “,29)),29)
Fig. 10 Extracting name and 29 signs
Now, we have the last name and many, many spaces before it (Fig. 11)
Fig. 11 Name with spaces
We have to delete them using the TRIM function. The TRIM function removes all spaces from a text string except for a single space between words. So, each space at the beginning and at the end of the string will be removed (Fig. 12)
=TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(“ “,29)),29))
Fig. 12 Trimming unnecessary spaces
After accepting it and copying it down, we have our last names (Fig. 13).
Fig. 13 Last names
Now, we have to find the thing between the first and the last name. We will use the SUBSTITUTE function. In the place of the first name, we will place nothing, i.e. an empty text string (Fig. 14).
=SUBSTITUTE(A2,B2,“”)
Fig. 14 Finding the middle name
Now, we have a shorter version of our name (Fig. 15).
Fig. 15 Shorter version
Let’s remove the last name now. We want to substitute the last name with an empty text string (Fig. 16)
=SUBSTITUTE(SUBSTITUTE(A2,B2,“”),D2,“”)
Fig. 16 Removing the last name
After entering and copying down, we have everything between the first name and the last name (Fig. 17). As you can see, we have also extracted spaces which we don’t need. So, let’s use the TRIM function to delete unnecessary spaces.
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,B2,“”),D2,“”))
Fig. 17 Unneeded spaces
Because we are editing whole column we can press Ctrl + Enter.
That’s how we extract first names, last names and everything in between with appropriate formulas.
