Recognising part numbers from within a text string and lookup prices

Hi everyone,

I have a very simple worksheet (in terms of layout) which contains all its data within a single column. For example:

(126476, Ghj67676767, 325452) blue table with 36 inch legs
14 (438585685) green chair with 40 inch legs
(ggfg556765, 456677) red box with brown lid
123 table types are available

The part numbers for each item (or set of items) are contained within the brackets. These are not always at the beginning of the data contained in each cell, but they are always in brackets and comma separated. As per the example, there are often two or three part numbers in a single cell (five is the maximum), followed by a description or other information. There are also other rows (such as the last line in the example above) which may contain or start with numbers but these are not in brackets so are not part numbers and should be ignored.

My aim is to extract the part numbers into separate columns so I can then look up prices from a separate worksheet.

Any help would be much appreciated.

Andy

Not clear to me.

If this is not what you want, need to see your expected result in a workbook.

Please Login or Register  to view this content.

Thanks, that does work - but it only worked on rows 24 to 26 for some reason.

Like I said,

Originally Posted by jindon

Not clear to me.

If this is not what you want, need to see your expected result in a workbook.

Please see the attached example - I have included a second worksheet showing the intended result.

This seems to work in some cases but not in others.

Please see the attached example - I have highlighted the first few rows where the sub generated no results or partial results.

If you just want anything in between brackes and separate by a comma if there is then.

Please Login or Register  to view this content.

+jindon, that works great! Many thanks for your help with this.

+protonLeah, I get an error on the following line:

PartCol = WorksheetFunction.Match(SrchStr, Range("1:1"), 0)