Extract part of the information in a single cell?

**Cidona** · 11-19-2010, 01:55 AM

Dear Forum,
Was hoping someone could tell me if the following is something that can be handled with a formula, VBA or not practical with Excel.

I have a worksheet that is an export from a 3rd party program that I want to extract information from. My initial problem is that there is a single cell with multiple 'parts' of data. And unfortunilty I can't get the 'Text to Columns' to help me out as the section that I'm looking to extract is enclosed with parentheses, but also are other sections that are not to be extracted, so using the parentheses wouldn't seem to work.

The only 'system' I can see is that the item code (which is the information I'm looking to separate), is always the last set of data enclosed within parentheses. Although it is not a set of number of spaces (depends on the item code lenght).

Please see attached file for an example of what I'm looking to do.

Feasable or SOL? Any insight or advise would be greatly appreaciated.

**outofthehat** · 11-19-2010, 04:20 AM

You can nest FIND functions to find the n-th instance of a character. So, if there are always 3 sets of brackets (bear with me, will improve this!), then the following will find the third open-brackets

=FIND("(",A1,FIND("(",A1,FIND("(",A1)+1)+1)

If there might be more than three, just nest as many as you might need.

If there are less than the number you've allowed for, you want the formula to ignore the iteration that generates it, so use IFERROR as per the attached.

Do something similar with LEFT to retrieve the other part.

**outofthehat** · 11-19-2010, 04:28 AM

Thinking about it, something simpler might do. Does this bracket always start with (Item Code? If so,

=FIND("(Item",A1)

Will give you the location where it starts, and

=RIGHT(A1,LEN(A1)-FIND("(Item",A1)+1)

will do the job.

**Cidona** · 11-19-2010, 10:37 AM

Hey outofthehat, Thank you for the reply!
Your 2nd suggestion is the pathway. The 1st suggestion wouldn't work as it depends on the particular part as to whether there are other items with parentheses within the cell or not. However, the item code which I'm looking to extract is always the last 'thing' within parentheses and as you picked up, always starts with (Item...
So your 2nd suggestion is just the ticket for populating Column D with the item code separated from the rest of the text (Very very cool!!).
Hate to push my luck here but since you are familar with the intent here and seem to have these text extraction formulas figured out...how would one go about populating say cell A12 with the text without the item code? (as shown on in A12 of the worksheet on the OP)?
Thanks again for your help. Nice kung-fu

**Cidona** · 11-20-2010, 02:18 AM

LOL. Swapped 'Right' for 'Left'. Good to go. Thanks again!

**Cidona** · 11-20-2010, 03:18 AM

Well actually just swapping left for right didn't produce enough characters for the full description, but with manupulation of the LEN function am able to get the desired results. Thanks again.