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.

1. 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:

= SUBSTITUTE(B1,” “,REPT(” “,255))

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.

= FIND(“#”,SUBSTITUTE(B1,” “,REPT(” “,255)))-100

This formula will locate the position of the first hash character (#) in a text string that returned by the SUBSTITUTE function.

= MID(SUBSTITUTE(B1,” “,REPT(” “,255)),FIND(“#”,SUBSTITUTE(B1,” “,REPT(” “,255)))-100,255)

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.

=TRIM()

The TRIM function removes all spaces from text string returned by the MID function, just leave one space between words.

2. Extract word that containing a specific character Using a User-Defined Function

Now, for our second method, we’ll explore a more advanced technique. By leveraging the power of VBA, we can create a custom function for this task. Follow these steps:

Step1: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step2: Right-click on any item in the Project Explorer, select Insert, and then choose Module.

Step3: Copy the provided VBA code and paste it into the module.

Function ExtractWordsWithChar(rng As Range, char As String) As String
    Dim cell As Range
    Dim words() As String
    Dim result As String

    For Each cell In rng
        words = Split(cell.Value, " ")
        For Each word In words
            If InStr(1, word, char) > 0 Then
                result = result & word & " "
            End If
        Next word
    Next cell

    ExtractWordsWithChar = Trim(result)
End Function

Step4: Save your work and close the VBA editor.

Step5: In any cell, type:

=ExtractWordsWithChar(B1, "#")

Note: Replace “B1” with the reference to your target cell.

Step6: Press Enter: The UDF will extract words containing the specified character.

3. Video: Extract word that containing a specific character

This Excel video tutorial where we’re unraveling the mysteries of data manipulation by exploring two methods to extract words containing a specific character. Our first method involves a potent Excel formula, ideal for those who appreciate the simplicity of functions. In the second method, we’ll venture into the realm of VBA, creating a custom function tailored for precision.

https://youtu.be/6ic8KIGcS4E

4. Related Formulas

• 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.…

5. Related Functions

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