Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.

In the image below we have a first name and last name in column A separated by a few different characters.

We can use three functions together to extract the initials. Because these are new functions let’s see how they work. We will start with the TEXTSPLIT function.

In the image below you can see the result of the TEXTSPLIT function.

This formula spills across the row to return the number of names in column A. Spilling is a new term that is associated with dynamic arrays, a new calculation engine in Excel. I have covered dynamic arrays in a separate blog post – see below.

New Dynamic Array Function SEQUENCE

Notice that you can use the array syntax within the TEXTSPLIT function. The array syntax which is the curly brackets { } allows you to list a number of different delimiters that you can split the name by.

We have listed the space, the comma, and the semicolon. If you needed other delimiters, you insert them between the curly brackets separated by a comma. Note you must enclose the delimiter within quotation marks.

In row 3 there is a comma and a space between the two names. This causes an extra empty spill cell. There is an argument in the TEXTSPLIT function that allows you to ignore empty cells. You can see the extra argument added in the image below.

We can now use an old function in combination with TEXTSPLIT. The LEFT function can extract text from the left of a text string. Its default calculation is to extract the first character. You can see the LEFT function used in combination with TEXTSPLIT in the image below.

Now that we have the initials we need to combine them. The new CONCAT function allows us to do that easily – see the image below.

This has been easier because we have only two names. What if there are more names? We will look at that in the next blog post.