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))

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.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.