Extracting and Splitting Text

Extracting and splitting text is about to get a lot easier.

Whilst Excel has functions like LEFT, MID, RIGHT, LEN, and SEARCH, unless you have a simple scenario, you’ll probably have to construct an unwieldy formula. Alternatively, you might use Text to Columns, Flash Fill or Power Query, but none of these is a perfect replacement.

Microsoft recently announced the arrival of several new functions, including TEXTBEFORE, TEXTAFTER and TEXTSPLIT. These are currently only available to Office Insiders on the Beta Channel.

𝗦𝘆𝗻𝘁𝗮𝘅𝗲𝘀

TEXTBEFORE — returns text that’s before delimiting characters.

• 𝚃𝙴𝚇𝚃𝙱𝙴𝙵𝙾𝚁𝙴 ( 𝚝𝚎𝚡𝚝 , 𝚍𝚎𝚕𝚒𝚖𝚒𝚝𝚎𝚛 , [𝚒𝚗𝚜𝚝𝚊𝚗𝚌𝚎_𝚗𝚞𝚖] , [𝚒𝚐𝚗𝚘𝚛𝚎_𝚌𝚊𝚜𝚎] )

TEXTAFTER — returns text that’s after delimiting characters.

• 𝚃𝙴𝚇𝚃𝙰𝙵𝚃𝙴𝚁 ( 𝚝𝚎𝚡𝚝 , 𝚍𝚎𝚕𝚒𝚖𝚒𝚝𝚎𝚛 , [𝚒𝚗𝚜𝚝𝚊𝚗𝚌𝚎_𝚗𝚞𝚖] , [𝚒𝚐𝚗𝚘𝚛𝚎_𝚌𝚊𝚜𝚎] )

TEXTSPLIT — splits text into rows or columns using delimiters.

• 𝚃𝙴𝚇𝚃𝚂𝙿𝙻𝙸𝚃 ( 𝚝𝚎𝚡𝚝 , 𝚌𝚘𝚕_𝚍𝚎𝚕𝚒𝚖𝚒𝚝𝚎𝚛 , [𝚛𝚘𝚠_𝚍𝚎𝚕𝚒𝚖𝚒𝚝𝚎𝚛] , [𝚒𝚐𝚗𝚘𝚛𝚎_𝚎𝚖𝚙𝚝𝚢] , [𝚙𝚊𝚍_𝚠𝚒𝚝𝚑] )

𝗘𝘅𝗮𝗺𝗽𝗹𝗲𝘀

On page 1, D3 contains the string ‘Andrew Charles Moss’. Below it, several current and new text extraction methods are shown in the respective tables.

Notice how frustratingly long the current methods are compared to the new ones? That’s because the former are based on finding the position of each space to determine where to extract from and to — as well as the number of characters to return. Moreover, the excessive use of SEARCH just to extract ‘Charles’ epitomises the complexity required for middle values.

In contrast, TEXTBEFORE and TEXTAFTER only need text and a delimiter supplied. This works for ‘Andrew’ and ‘Charles Moss’, but ‘Andrew Charles’ and ‘Moss’ also have an [𝚒𝚗𝚜𝚝𝚊𝚗𝚌𝚎_𝚗𝚞𝚖] of 2 to start the extraction from the second space. Furthermore, ‘Charles’ is extracted — first by using TEXTSPLIT to separate each part — and then INDEX to return the second array element.

Page 2 shows a current way of splitting text. The page 1 formulas for the first, middle and last names are placed in the CHOOSE function, which contains {𝟷,𝟸,𝟹} in the 𝚒𝚗𝚍𝚎𝚡_𝚗𝚞𝚖 argument to spill them across the cells.

The aforementioned TEXTSPLIT does the same thing, but with a significantly shorter formula.

#excel #exceltips #excelformulas