Today, we are going to combine text with two, different delimiters. In our example, we have shop and street names. We want to put a semicolon between each shop and street name. However, between street and shop names we want to put another symbol, which is a pipe. What is important in our example, is the fact that not all cells are filled. We want to just ignore them while combining text (Fig. 1).
Combine text with different delimitersFig. 1 — Text to combine
This task is very simple in Excel 2019 and newer versions because we have the TEXTJOIN function. In the first argument of this function, we are putting the delimiter. We can even put more than one delimiter. In our example, the delimiters are placed in cells D9 and E9, so let’s put them in the formula and let’s lock them by pressing the F4 key. When we put more than one delimiter in the TEXTJOIN function, the function will use them alternatively, which means that first, it will use the semicolon, then the pipe, then the semicolon and so on. In next argument we have to decide whether we want to ignore empty cells or not. In this case, as I said earlier, it’s very important for us to ignore them, so we are writing TRUE or 1. The last argument we need is just text, i.e. the range with text. Let’s close the formula (Fig. 2)
=TEXTJOIN($D$9:$E$9,1,B2:G2)
Fig. 2 — Formula for combining text
After putting it into the cell, let’s copy it down. We have the results. We can see there are semicolons between shop and street names, and pipes between street and shop names (Fig. 3).
Fig. 3 — Combined text
In the TEXTJOIN formula, delimiters orientation is not important. They can be horizontal or vertical.
We can even hardcode them here by selecting first argument and pressing the F9 key to evaluate it. Now, they are written as text (Fig. 4).
=TEXTJOIN({“;”,”|”},1,B2:G2)
Fig. 4 — Hardcode delimiters
We can leave them like that and the formula will work the same.
Today, we are going to extract middle names from full names. However, not everybody has got a middle name, so let’s start with the first and the last name. We can use here the Flash Fill option, which means that we just have to start writing the first name, then another first name. After that, the Flash Fill command does the work for us (Fig. 1).
Extract middle names with Flash FillFig. 1 — Using the Flash Fill option
All we need to do is accept the results with Enter, and we have the first names (Fig. 2)
Fig. 2 — Entering the results
We do the same with the last name, i.e., we start writing the last name in the first cell, but this time we will force the Flash Fill option to work by pressing the Ctrl + E keyboard shortcut (Fig. 3). This way, we have last names (Fig. 4)
Fig. 3 — Flash FillFig. 4 — Flash Fill results
Now, the hardest part – middle names. We start writing the middle name in the first row, however, we don’t have a middle name in the second row. This time we’ll also use the Flash Fill option (Fig. 5)
Fig. 5 — Flash Fill for middle names
When all middle names have been extracted, we can see an error. Edward isn’t a middle name. We have to correct the Flash Fill answer. We can do it, until the Flash Fill Options icon is showing up (Fig. 6).
Fig. 6 — Flash Fill icon
We have to change the middle name, which, in fact, isn’t a middle name, for a different mark, e.g. an exclamation mark. We simply write the mark in the place of the error, press Enter, and the Flash Fill option will do its magic again (Fig. 7) Exclamation marks are in the rows, where there aren’t middle names.
Fig. 7 — Exclamation marks
We can put almost anything in the place of the exclamation mark, e.g. a star (Fig. 8).
Fig. 8 — Stars
We usually don’t want any stars or other signs. We want to have blank cells, however, the Flash Fill option doesn’t work with spaces. That’s why we can use the Filter option, which we can find in the Data tab (Fig. 9). In the Filter option, we want to show only this sign (a star).
Fig. 9 — Filter option
Now, we’re left only with stars (Fig. 10). We have to select the range (Ctrl + Shift + ↓) and press the Delete key to delete all the signs.
Fig. 10 — Selected range
Then we can turn off the filter. We can do it manually, by pressing the filter icon and selecting the Clear Filter From “Middle names” option (Fig. 11) or by pressing the Ctrl + Shift + L key combination.
Fig. 11 — Turning off the filter
Now, we have middle names only in places, where they really exist, and blank cells, where there aren’t any middle names (Fig. 12)
