Extracting text from a string in Excel

Not too long ago, extracting text in Excel was a bit of a nightmare. Sure, we've always had functions like LEFT, RIGHT, MID, LEN, and FIND to help us, but even the simplest extractions could require a lengthy formula.

That changed last year, as Microsoft announced a new set of text functions that made things significantly easier for us:

➤ TEXTBEFORE — return text from a value that is before a specific delimiter.
➤ TEXTAFTER — return text from a value that is after a specific delimiter.
➤ TEXTSPLIT — split text into rows or columns according to a specific delimiter.

Video example

The text string 'Global Excel Summit' is contained in cell A2. This is referenced in the following formulas to extract certain parts:

'Global'
=𝚃𝙴𝚇𝚃𝙱𝙴𝙵𝙾𝚁𝙴(𝙰𝟸," ")

'Global Excel'
=𝚃𝙴𝚇𝚃𝙱𝙴𝙵𝙾𝚁𝙴(𝙰𝟸," ",𝟸)

'Excel Summit'
=𝚃𝙴𝚇𝚃𝙰𝙵𝚃𝙴𝚁(𝙰𝟸," ")

'Summit'
=𝚃𝙴𝚇𝚃𝙰𝙵𝚃𝙴𝚁(𝙰𝟸," ",𝟸)'

Excel'
=𝙸𝙽𝙳𝙴𝚇(𝚃𝙴𝚇𝚃𝚂𝙿𝙻𝙸𝚃(𝙰𝟸," "),,𝟸)

The delimiter used in all of them is a space enclosed in quotation marks (" "). By default, the first instance of a space is found first; however, setting the [𝚒𝚗𝚜𝚝𝚊𝚗𝚌𝚎_𝚗𝚞𝚖] argument to 2 ensures the second occurrence is prioritised.

Extracting 'Excel' is more challenging than the others because it's in the middle of the string. That's why splitting the whole value first with TEXTSPLIT and then setting INDEX's [𝚌𝚘𝚕𝚞𝚖𝚗_𝚗𝚞𝚖] argument to 2 is necessary to only return one value.

On a final note, don't forget there are other ways to extract text as well. Text to Columns, Flash Fill, and Power Query are all alternatives!

For more Excel tips and tricks like this, check out our Video Tutorials page.