Quick, clean, and to the point

Extract word that begins with specific character

Excel formula: Extract word that begins with specific character
Generic formula 
=TRIM(LEFT(SUBSTITUTE(MID(txt,FIND("@",txt),LEN(txt))," ",REPT(" ",100)),100))

To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text.

In the example shown, the formula in C5 is:

=TRIM(LEFT(SUBSTITUTE(MID(B5,FIND("@",B5),LEN(B5))," ",REPT(" ",100)),100))

How this formula works

Starting from the inside out, the MID function is used to extract all text after "@":


The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the exact number of characters to extract. MID doesn't care if this number is bigger than the remaining characters,  it simply extracts all text following "@".

Next, we "flood" the remaining text with space characters, by replacing any single space with 100 spaces using a combination of SUBSTITUTE and REPT:

SUBSTITUTE("@word and remaining text"," ",REPT(" ",100))

This seems crazy, but the logic becomes clear below.

Next, to extract just the word we want (i.e. @word), we use LEFT to extract the first 100 characters from the left. This gets us "@word", plus many extra spaces. To visualize, the hyphens below represent spaces:


Now we just need to remove all extra spaces. For that, we use the TRIM function.

Note: 100 represents the longest word you expect to find that begins with the special character. Increase or decrease to suit your needs.

Got a better way to do this? Let me know in the comments below!

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables