CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
user5540790
user5540790

Reputation: 11

Extract specific word cells

I have a description field that contains multiple text. I want to extract a certain data that I need. Which is the "Group Code" and "Group Name". The text length of each cell might be different.

What formula can assist me to grab those two fields?

An example of what my description field look like and what I hope to extract from it on column "D" and "E":

    Sample Data

Upvotes: 1

Views: 82

Answers (1)

user4039065
user4039065

Reputation:

Standard text parsing methods should be able to do this. The FIND function and SEARCH function can locate the static text labels as well as the CHAR(10) line feed that terminates the entry. The MID function peels out the desrired subtext while CLEAN and TRIM tidy up the result. Finally, the IFERROR function substitutes a zero-length string instead of an error code (e.g. #VALUE!) if the text parsing is unsuccessful.

    Text Parsing

The formula to get the Group Code in D2 is,

=IFERROR(TRIM(CLEAN(MID(B2, SEARCH("group code:", B2)+LEN("group code:"), FIND(CHAR(10), B2, SEARCH("group code:", B2))-(SEARCH("group code:", B2)+LEN("group code:"))))), "")

Fill down as necessary. Make the appropriate adjustments to the formula to retrieve the Group Name to E2.

Upvotes: 0

Related Questions