1

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

Community
  • 1
  • 1
user5540790
  • 11
  • 1
  • 4
  • Regex is the cleanest way. Here's [another question](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) that'll get you started. – Tom Woodward Nov 09 '15 at 02:46

1 Answers1

0

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.

  • I tried entering your formula in the field but it didn't work. Can you copy and paste it below. Maybe I entered something wrong. Thanks – user5540790 Nov 09 '15 at 03:17
  • Maybe your computer's regional system uses a semi-colon instead of a comma as a list separator in the system's regional settings. tbh, **'it didn't work'** isn't much to go on. –  Nov 09 '15 at 03:19
  • Thanks @Jeeped You are awesome! – user5540790 Nov 09 '15 at 03:31