I was recently asked by someone who was working their way through one of my online courses to help them find the most efficient way of extracting a client name from a single column of data. The cells in the column could contain not only the client name, but also additional text as well. Obviously, to be able to do this, there needs to be some sort of consistent delimiter between the client name and the rest of the contents of each cell. In this case, the first issue was coping with the possibility that some cells only contained the client name, and therefore didn’t include any delimiter at all. The second issue was dealing with inconsistent use of delimiters.

There are a few ways to extract text strings using a delimiter, including using Power Query which would be particularly appropriate if several other processing steps were also required. However, we will concentrate on using some Excel functions.

In the days before the introduction of the advanced text functions TEXTBEFORE(), TEXTAFTER() and TEXTSPLIT(), it would have been necessary to use the FIND() or SEARCH() functions to discover the position of the delimiter character or characters, and then to use LEFT(), RIGHT() or MID() text functions as appropriate to extract the string of text characters that represent the client name. In our case, this would have meant using the LEFT() function to extract the characters before the delimiter. Now, the TEXTBEFORE() function makes the process much simpler:

If we use the simplest form of TEXTBEFORE() we just need the first two arguments, the first is the text from which we want to extract our client name, and the second the delimiter character or characters to use. It’s worth stating at the outset that this method will only work if no client names include our delimiter. We are starting by looking for the delimiter set of characters: space dash space:

=TEXTBEFORE([@[Sales details]]," - ")

As we can see, this works for those cells that do contain space dash space, but where that combination of characters does not exist, our function returns #N/A.

Dealing with those cells that only contain the client name is easy enough. One of the optional parameters available in TEXTBEFORE() is the match_end argument. The default setting is ‘0-Don’t match to end’. This means that if our delimiter doesn’t exist in the text, the function will return #N/A as we can see above. We can set this argument to ‘1-Match to end’. This causes TEXTBEFORE() to match the delimiter to the end of the string if no actual match is found, thus including the entire text string:

=TEXTBEFORE([@[Sales details]]," - ",,,1)

Although this solves the issue with the absence of the required delimiter, we have two cells where a delimiter has been used, but not quite the one that we were expecting. In cell A8 the delimiter is just a dash, with no trailing or leading spaces, and in A13 we have two consecutive dashes.

In these days of dynamic arrays, it’s much easier than it used to be to use lists of items in certain functions. Accordingly, we can use a list of delimiters as our second argument in the TEXTBEFORE() function. We designate a list of values by surrounding the list of values in braces {}, and separating each one with commas: {" - ","-","--"}

Here is our list of delimiters included in our function:

=TEXTBEFORE([@[Sales details]],{" - ","-","--"},,,1)

This works, but it would require the formula to be edited if we found that other forms of delimiter had been used. It would be better to enter our delimiters in cells in a Table column. This makes them more visible, and it also means that we can add delimiters to our Table just by adding a row to our Table, and it would automatically be used in our TEXTBEFORE() function. Here we have added our Table and given it the name Delimiter. To enter our delimiters as text we have preceded each of them with a single apostrophe. We have also changed our function to include a reference to our Delimiter table in place of our list of delimiters:

Note that we have also added a row to our Sales table with the Sales details value including ‘space slash space’ as the delimiter. If we add this new delimiter to our Table, we can see that our function now automatically retrieves our client name correctly:

Following on from the recent article on using the new GROUPBY() function, we can use our TEXTBEFORE() formula within GROUPBY() to group our Sales values by client name in a single formula:

=GROUPBY(TEXTBEFORE(Sales[Sales details],Delimiters,,,1),Sales[Sales],SUM)

We have added a row with a second reference to the client Blue soul just to prove that the grouping works.

You can explore all aspects of Excel, including the range of text functions, in the ICAEW archive:

• Excel archive