Sometimes, when we have formula results, we want to have only the text, i.e. the answer (Fig. 1).
Paste as values, the Paste Special windowFig. 1 — TRIM function results
We can copy the results, then right click a target cell and choose the Paste Values option from the pop-up menu (Fig. 2).
Fig. 2 — Pop-up menu — Paste values
And, we have just text not formula (Fig. 3).
Fig. 3 — Pasted values (text)
There is one more solution. When we copy our formula, we click on a target cell and press Alt + Ctrl + V combination to show the Paste Special window (Fig. 4). In the Paste area, we click Values, in the Operation area, we choose None, then we press OK.
Fig. 4 — Paste Special window
Now, we have the results only once. We don’t need the extra column, so we must delete it (Fig. 5)
Today, we are going to remove unnecessary spaces. Sometimes, there’s a space at the beginning of text, sometimes, there are more than one space between two words, and sometimes, there are some spaces at the end. The last ones are the hardest to notice (Fig. 1)
Remove unnecessary spaces — TRIM functionFig. 1 Unnecessary spaces
The name in the first line, Brave Brandon with a space at the end, is something different for Excel, than Brave Brandon without a space. In such a case, we want to use the TRIM function to remove the space (Fig. 2)
Fig. 2 TRIM function
After copying it down, we have our names without unnecessary spaces (Fig. 3)
Fig. 3 No unnecessary spaces
This simple function removes unnecessary spaces from the beginning, the end, and the middle of text beetween words only leaves one space.
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.
