CODEWITHSUNDEEP
stringexcelexcel-formulatext-extraction
Miaka3
Miaka3

Reputation: 405

Extract a Word from String Containing a Specific Character within Substring

In MS Excel I would like to use a formula 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

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

A6: There was a tremendous amount of raing State=Oklahoma

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

B5: dinner=lastnight

B6: State=Oklahoma

I've attempted several approaches, some closer than other, but not able to figure it out, if at all possible.

Upvotes: 1

Views: 4644

Answers (2)

user4039065
user4039065

Reputation:

Use an old text parsing trick that greatly increases the distance between the words with repeating zeroes through the SUBSTITUTE and REPT functions which affords a larger swipe of the intended substring.

      Parse Substring with SUBSTITUTE and REPT

The formula in B2 is,

=TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 99)), MAX(1, FIND("=", SUBSTITUTE(A2, " ", REPT(" ", 99)))-50), 99))

The TRIM function (used as a wrapper) removes leading and trailing spaces.

Upvotes: 2

12V_man
12V_man

Reputation: 36

I used 5 helper columns to do this; they could be collapsed into one very long forumula, but I prefer it this way (easier to track down source of any errors I think). Given the strings you have in A2; In B2 this finds location of "=" 

=SEARCH("=",A2)

In C2 this finds the instance number of the " " preceeding the "=" 

=B2-LEN(SUBSTITUTE(MID(A2,1,B2)," ",""))

In D2 this notes the location of that " " 

=SEARCH(CHAR(33),SUBSTITUTE(A2," ",CHAR(33),C2))

In E2 this locates the " " trailing the "=", or if the "=" appears in the final word in the cell it notes the full cell length +1 

=IFERROR(SEARCH(" ",A2,B2),LEN(A2)+1)

Using these values in F2 this pulls the string from the preceeding " " to the trailing " " 

=MID(A2,D2+1,E2-D2-1)

Upvotes: 0

Related Questions