Need to capture a specific string of characters

**saccer** · 08-23-2012, 01:32 AM

I need some help in capturing a specific string of characters within a larger string. Following is an example cell:

Glutathione [ C10 H17 N3 O6 S, tgt=93.62, overall=93.62, CAS ID=70-18-8, KEGG ID=C00051 ]

All I want to retain is C10H17N3O6S. Having removed the extra spaces. It looks like LEFT, MID and RIGHT functions will not work because the start of the string is variable, additional examples:

L-Aspartate [ C4 H7 N O4, tgt=81.15, overall=81.15, CAS ID=56-84-8, KEGG ID=C00049 ]
Dodecanoic acid [ C12 H24 O2, tgt=98.79, overall=98.79, CAS ID=143-07-7, KEGG ID=C02679 ]

I need a formula that will capture all text between the characters "[" and ",". Once that is complete I need to use a TRIM function to remove all spaces. A solution here will save me 6-8hrs prior to a Monday morning deadline. Thank you in advance for any input.

**vlady** · 08-23-2012, 01:41 AM

try this one.
assuming the values are starts in A1

Please Login or Register  to view this content.

**dilipandey** · 08-23-2012, 01:43 AM

HI Saccer,

Welcome to the forum.

Use the below formula:-

Formula:

Please Login or Register  to view this content.

capture specific string.xlsx

Regards
DILIPandey

<click on below 'star' if this helps>

**Ace_XL** · 08-23-2012, 01:43 AM

Use..

=SUBSTITUTE(MID(A1,2,FIND(",",A1)-2)," ","")

**saccer** · 08-24-2012, 01:10 AM

Thank you very much! Those formulas work and you have saved me a ton of time!

Need to capture a specific string of characters

LinkBack

Thread Tools

Rate This Thread

Display

Need to capture a specific string of characters

Re: Need to capture a specific string of characters

Re: Need to capture a specific string of characters

Re: Need to capture a specific string of characters

Re: Need to capture a specific string of characters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions