Returning common substrings

[Skoogh]

Hi everyone,

First off, LOVE this forum. Has helped me a lot before. I am however feeling stuck this time around and haven't really found a good solution.

I have a lot of rows with various product names, what I want to do is to go through the names and return some of the substrings in order to group them properly.

Sample data

Return String	Product Name
Teddy Bear	1234 - Teddy Bear 56x78 white
Teddy Bear	4321 - Teddy Bear 80x90 Brown
Mop with Handle	6789 - Mop with golden Handle
Mop with Handle	1011 - Mop with white Handle
etc	etc

As you can see, part of the things I would like to disregard are things like colour and dimensions. I've been dabbling with the text to columns to build new strings, partly automatic but also a lot of manual work involved in that. And it would be great if I could find a code to do this instead as I would like to do this in numerous sheets.

I believe the solution would look something like a for loop going through every item and then a new for loop going through all other items to find the common substrings and then return them in the "Return String" column. Would that work and how would it look like? is there another/better way?

Thanks a lot in advance!

 

[yky]

Seems to me you want to run a series of SUBSTITUTEs to a) replace digits 0 - 9 to blank, b) all color words to blank, c) hyphen to blank, d) " x " to blank, e) double space to single space.

If you apply that to the examples you gave above, you'll get the two return strings you want.