Extract a Whole Word From String Containing a Specific Letter or Character

Question

I would like to extract only the word from a cell that contains a specific character ("=") within the text.

A2:  Dolly made me a homemade=cake and some muffins
A3:  we had cheese=cake for dinner
A4: Everyone loves how the bakery makes some awesome=cakes

I would like the following from in column (A2:A4) to provide the following results in column (B2:B4).

B2:  homemade=cake
B3:  cheese=cake
B4:  awesome=cakes

I have tried the following solution with worksheet functions.

A2: Johnny made his own dinner=lastnight and then cleaned the kitchen

=TRIM(TRIM(IFERROR(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH(" ",A2&" ",SEARCH("=",A2))-1)," ",REPT(" ",LEN(A2))),LEN(C246)),""))),""),

This formula does not work as you may see, as it only extracts data to the right of the ("=") symbol with the following results . Whereas, I'm seeking a solution that will extract the whole word (right and left) of where the ("=") symbol.

Answer 1

Try the following User Defined Function (UDF)

Public Function Equals(inpt As String) As String
   Equals = ""
   ary = Split(Application.WorksheetFunction.Trim(inpt), " ")
   For Each a In ary
      If InStr(1, a, "=") > 0 Then
         Equals = a
         Exit Function
      End If
   Next a
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=equals(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#1:

You can do this without VBA, using the same methodology. With data in A1, in C1 enter:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across. Then in B1 enter:

=INDEX(C1:IV1,MATCH("*=*",C1:IV1,0))

Answer 2

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation. Example: Suppose that cell A1 has the text string: “Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function: =LEFT(A1,5) The result is “Creat” 2. To get the last 11 characters of the string, we use the RIGHT function: =RIGHT(A1,11) The result is “certainties” 3. To get 7 characters from the string starting with position 10, we use the MID function: =MID(A1,10,7) The result is “y requi”. 4.To get the first word of the string, we use the LEFT and FIND functions: =LEFT(A1,(FIND(” “,A1)-1)) The result is “Creativity”. - See more at: http://www.exceldigest.com/myblog/2009/02/01/how-to-extract-text-from-another-text-string/#sthash.yNDHU7Xe.dpuf