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
@globalexcelsummit 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. 𝗩𝗶𝗱𝗲𝗼 𝗲𝘅𝗮𝗺𝗽𝗹𝗲 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! #exceleration #excel #microsoftexcel #excelformulas #exceltips #globalexcelsummit ♬ original sound - globalexcelsummit
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.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.