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.
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:
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!
The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression: SEARCH ( "(" , B5 ) + 1...
At the core, this formula takes a text string with spaces, and "floods" it with additional spaces by replacing each space with a number of spaces using SUBSTITUTE and REPT . The number of spaces used is based on the overall length of the original...
FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars. The LEFT function then extracts characters starting at the the left side of the...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Some Excel formulas are complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
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.