This post will guide you how to extract word that containing a specific character using formula in excel. How to get a word that contains a specific character (such as: @, comma, hash, etc.) in a text string in one cell.
Extract word that containing a specific character
If you want to extract word that contains a hash character in a text string in Cell B1, you can use a combination of the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the FIND function to create an excel formula as follows:
=TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",255)),FIND("#",SUBSTITUTE(B1," ",REPT(" ",255)))-100,255))
Let’s see how this formula works:
The REPT function will return a new text string that contains 255 empty spaces. And it goes into the SUBSTITUTE function as its new_text argument. Then the SUBSTITUTE function will replace all empty string with new text value returned by the REPT function.
This formula will locate the position of the first hash character (#) in a text string that returned by the SUBSTITUTE function.
This MID function will extract 255 characters from a text string (returned by the SUBSTITUTE function) at a specific position that returned by the FIND function. Then the word that contain hash character will be returned.
The TRIM function removes all spaces from text string returned by the MID function, just leave one space between words.
- Split Multiple Lines from a Cell into Rows
If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
- Extract word that starting with a specific character
Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…
- Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])….
- Excel TRIM function
The Excel TRIM function removes all spaces from text string except for single spaces between words. You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
- Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
- Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
- Excel REPT function
The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT (text, number_times)…