Parsing out Sizes/Colors from text string

[BlakeSkate]

Hey guys!
I have a list of 20,000+ items where i need to know what the size, color, and if/what pads they are. so lets do this:
When i run the macro it looks for text from a table (see attached) and puts the size in B and color in C and pads in D
Here is the tricky part:

In regards to color: it has to be whatever color appears first in the text string. There can be multiple color combos, but the first color is the primary color. So an example would be:
Enjoi Black/Red trucks = Black
Enjoi Red/Black trucks = Red

In regards to pads: most of the items arent pads so just leave it blank if there is an error (same goes for color and size as some items might not contain any.

I hope you can help me, and all help is greatly appreciated!

[BlakeSkate]

Bump :x
-
Really any VBA that takes a table and matches will be helpful because i think i'll be able to take it from there.

[protonLeah]

This code will handle colors and pads. I haven't figured out how to find sizes yet. I don't understand the size table
and there is nothing in the sample products that looks like it.

Please Login or Register  to view this content.

[BlakeSkate]

nothing in the sample products that looks like it.

Awesome!
Let me go in-depth with the sizes

I updated the size tables in the attachment
So what i would like for this to do is if the text string in 'Product Name' says "Complete" then use the table named "Completes" where if it finds the Description place the Value in the Size column
If it doesn't say complete use the first table where if it finds the Description and places the Value in the size column

so for the 3 examples it should look like this:
4MihXBX.jpg

thanks for your help so far protonLeah

[BlakeSkate]

Hey protonLeah,
I really like how the code functions. I see that (for pads) its grabbing the wrod prior whenever it sees "Pads". Is it possible to have it only grab the FIRST "Pads" meaning there is not more than one result? The same with color would be AMAZING. You're a life saver!

[jindon]

See if the attached is how you wanted, otherwise need to see your desired result in a workbook.

[BlakeSkate]

otherwise need to see your desired result in a workbook.

jindon,
i did post a picture of the desired result, and this is what i want, but with the other sizes as well "Xs" and "Adjustable" are sizes.
I'll post up the same workbook but with the desired results. I also added more example items for a better understanding
Thanks so much for helping me!

and allow me to elaborate a little more on sizes:
The second table (Labeled "Completes") should only match if the text string from the Products sheet contains "Complete" or "Deck"
for everything that DOESN'T have "Complete" or "Deck" it should run through the first table (Labeled "Size Table")

"Size Table" contains words and numbers to identify sizes like "Small- Medium - Large" and so on.
Again thank you so much for helping me.

[protonLeah]

Please Login or Register  to view this content.

[jindon]

Changed the structure in Size table sheet.
Separate the normal and Inc...

[BlakeSkate]

Jindon that works perfectly on all 20,000 items!
if i add sizes / colors do i have to respecify a range or is it grabbing from the entire sheet?

[jindon]

It should automatically generate the pattern if you add to the each table without any gap.

[BlakeSkate]

you're amazing thank you so much

It should automatically generate the pattern if you add to the each table without any gap.

[jindon]

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[BlakeSkate]

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hey Jindon,
So i'm running into a problem where sometimes the name of the product contains numbers! i.e "Shake Junt Triple Og'small A 7 Bearings Single Set Skateboarding Bearings"
where the size is displaying as "7". Instead i would like to prioritize the word "Bearings" and have the size go to 7/8, but the "A 7" appears BEFORE the word "Bearings". Can it be possible to prioritize the order of the size chart?

So lets say this is the size chart:

Size: Bearings Value: 7/8
Size: 1 1/7 Value: 1 7/8
Size: 7 Value: 7
Size: 1 Value: 1

So "Shake Junt Triple Og'small A 7 Bearings Single Set Skateboarding Bearings" would appear as size "7/8" rather than 7
and "Shake Junt Triple Og'small A 7 Bleak 1 1/7 Single Set Skateboarding Things" would appear as size "1 1/7" rather than "7" or "1"

[BlakeSkate]

Thanks.

Also a separate issue is that sometimes the specified size text is conjoined with part of the other text
Here is a screenshot of what i'm talking about where the size and the word "phillips" are holding hands, and thus the search for the text string does not pick it up
the only thing i can think of is

Please Login or Register  to view this content.

, so i don't know if AFTER the original search is done you would be able to put it through a second search that looks for the text whether its conjoined with something else or not? i wouldn't even know where to begin :X

thanks!

67sv2SL.png

[jindon]

Can you post a workbook with the data and desire result?

[BlakeSkate]

Can you post a workbook with the data and desire result?

Here you are!
Also i don't think the "completes" are taking from the second size chart because some are showing as "small", "medium", and "large"
thanks for helping me again!

[jindon]

You need to change the Size Table.

Copy higher priority size to the "Completes". (I've done only Bearings 7/8")
and the code is changed as attached.

[BlakeSkate]

You need to change the Size Table.

Copy higher priority size to the "Completes". (I've done only Bearings 7/8")
and the code is changed as attached.

Awesome let me mess around and see if this will work

[BlakeSkate]

You need to change the Size Table.

Copy higher priority size to the "Completes". (I've done only Bearings 7/8")
and the code is changed as attached.

Okay cool, this is going to be very hard to organize correctly, but it will get the job done! thank you so much Jindon

[BlakeSkate]

Hey Jindon,
With your code i've noticed something that took me a bit to figure out;

When it is searching for sizes through the "Product Name" column its grabbings and searching from left to right. Meaning if i want to search for 1.5" in " 11/1.5" " it will not show up because there is an "11/" in front of it. BUT if its 1.5"/11 it will show the 1.5" because its going from left to right. Is there any way to have it do both left AND right?

I've attached an example

examplejindon.xlsm

[jindon]

Sorry, I didn't get email notification for this or missed....
Try change to

Please Login or Register  to view this content.

[BlakeSkate]

Sorry, I didn't get email notification for this or missed....
Try change to

Please Login or Register  to view this content.

perfect, thank you for always helping!