Find size data from Item Title column to create a Size column

[frewert]

Originally the workbook only contained Column A and the intention is to create Column C (Size Column).

Here is my workbook so far.

Size project method A.xlsm

drive.google.com

Here is an image preview.

https://imgur.com/a/nOEYEbC

I have highlighted the ones that aren't working as intended and put a column of what the desired result should be.

You will notice I have columns F and G that are valid sizes and conversions.

My goals now:

• Item Titles with (Number)X(Number) should return the first number. E.g., 32X30 should return 32.
• Item Titles with M/L are returning nothing. It should return L (the largest size)
• Item Titles ending in 0,2,4,6,8,S,M,L are not returning a result. Something to do with not being surrounding by the space character.
• Item Titles with 6S, 6R, 6M, 6L, 6T should return 6. (Should work with all numbers, not just 6 in example)

Here is the vba code used. (credit: reddit.com/user/arkangelshadow007)

Sub shoe_sizes()

Dim items As Range, validSizes As Range, conversion As Range

Set validSizes = Range("f2:f200") 'adjust ranges
Set conversion = Range("g2:g200")
Set items = Range("a2:a4200")

For Each Item In items.Cells

    For Each validSize In validSizes.Cells

        x = InStr(Item.Value, validSize.Value)

        If x > 0 Then

            Cells(Item.Row, 3) = Cells(validSize.Row, 7)

            GoTo next_item

        End If

    Next validSize

next_item:

Next Item

End Sub

Thank you!

 

[Akuini]

Ok, this code uses WorksheetFunction.Sort, which is available only in Excel 365 or newer. I need to sort the variable vb, which holds the values of column H (Category), by the length of the words to make the Instr function work correctly.
It would be best if you could provide about 100 sample rows that represent data supposed to match all "groups" of criteria, so we can easily check whether it works as expected or not.
Also, in the Immediate Window you can see same info regarding data size & time processing.

Sub frewert_3()
Dim regEx As Object, Matches As Object
Dim i As Long, j As Long, n As Long
Dim tx As String, pt
Dim va, vb, vc, ary
Dim Flag As Boolean

t = Timer
With Range("F2", Cells(Rows.Count, "F").End(xlUp)).Resize(, 2)
    .Value = Application.Trim(.Value)  'trim trailing space in data in col A
    vb = .Value
End With



va = Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'populate va with col A data
ReDim vc(1 To UBound(va, 1), 1 To 1)

Debug.Print "Number of records  :" & " : " & UBound(va, 1)
Debug.Print "Number of Size List  :" & " : " & UBound(vb, 1)

For i = 1 To UBound(va, 1)
    tx = va(i, 1)
    
    For Each x In Split(", / \ ( ) . ; : [ ]", " ") 'replace some characters with a space (exclude ")
        tx = Replace(tx, x, " ")
    Next
    tx = " " & tx & " "   'add space at the beginning and end of each record
    va(i, 1) = Replace(tx, """", "ZZZ")  'replace " with ZZZ so word/number with " will be ignored
Next

    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
    ary = Split("Ties|Scarves|Wallets|Hats|Bags", "|")
            
'            ============= Size =============================================
            For i = 1 To UBound(va, 1)
                Flag = False
                tx = va(i, 1)
                
                For Each x In ary  'deal with "Ties|Scarves|Wallets|Hats|Bags"
                     If InStr(tx, " " & x & " ") Then Flag = True: Exit For
                Next
                
                If Flag = False Then   '
                    If InStr(tx, " " & "Torrid" & " ") Then           'deal with Torrid
                        For Each x In Split("0 1 2 3 4 5 6", " ")
                            If InStr(tx, " " & x & " ") Then
                                vc(i, 1) = x & "X"
                                Flag = True: Exit For
                            End If
                        Next
                    End If
                End If
                
                If Flag = False Then
                    For j = 1 To UBound(vb, 1)
                        If InStr(tx, " " & vb(j, 1) & " ") Then  'check if a record has items in list in col F
                            vc(i, 1) = vb(j, 2)                  'if True then add col G value to variable vc
                            Flag = True: Exit For
                        End If
                    Next
                End If
                
                If Flag = False Then 'if the above cheking give empty result
                    For Each pt In Split(" [0-9]{2}X[0-9]{2} | [0-9]{1,2}[MRLST] ", "|")  'use this regex pattern to check
                        .Pattern = pt
                        If .test(tx) Then
                            Set Matches = .Execute(tx)
'                             Debug.Print i & " : " & Matches(0) & " : " & Val(Matches(0))
                            vc(i, 1) = Val(Matches(0))    'if there's a match then add the match to variable vc
                            Flag = True: Exit For
                        End If
                    Next
                End If
           

            Next
            
'===================== Category ======================

    With Range("H2", Cells(Rows.Count, "H").End(xlUp)).Resize(, 3)
        .Value = Application.Trim(.Value)                          'trim trailing space in data in col H:I
        vb = .Value                                                'load data to vb from col H:I
    End With
    
    For i = 1 To UBound(vb, 1)
        vb(i, 3) = Len(vb(i, 1))  'load col H word's length to 3rd col in vb
    Next
    
    vb = WorksheetFunction.Sort(vb, 3, -1)  'sort vb by word's length

    
    ReDim vc(1 To UBound(va, 1), 1 To 1)
        For i = 1 To UBound(va, 1)
            Flag = False
            tx = va(i, 1)
            For j = 1 To UBound(vb, 1)
                If InStr(tx, " " & vb(j, 1) & " ") Then  'check if a record has items in list in col H
                        vc(i, 1) = vb(j, 2)                  'if True then add col I value to variable vc
                        Flag = True: Exit For
                End If
            Next
        Next
            
End With
'put the result (of Category) in col C:
Range("C2").Resize(UBound(vc, 1), 1) = vc
Debug.Print "Number of Category List  :" & " : " & UBound(vb, 1)
Debug.Print "Completion time:  " & Format(Timer - t, "0.00") & " seconds"

End Sub

Size project method A-01.xlsm
	A	B	C	D	E	F	G	H	I
1	Item Title	Size				List of valid size entries	replace entries	Category
2	Lands End Denim Shirt Womens M 10/12 Cotton Vintage Long Sleeve Button Up	M	Tops			7XP	7XP	Top	Tops
3	Lululemon 11" Shorts Mens XL Black Zip Pockets Elastic Waist Drawstring Vintage	XL				6XP	6XP	Blouse	Tops
4	LC Lauren Conrad T-Shirt Womens 2XL XXL Plus Crew Short Sleeve Cotton Floral	2XL	Tops			5XP	5XP	Tee	Tops
5	Dickies Long Sleeve Pocket Tee Mens 3XL XXXL Orange Relaxed Performance Wicking	3XL	Tops			4XP	4XP	Tunic	Tops
6	Croft & Barrow Blouse Womens 2X Blue 3/4 Sleeve Knit Spandex	2X	Tops			3XP	3XP	Kimono	Tops
7	Chicos Travelers Dress Womens 3 (US: XL) Black Acetate Spandex Sheath Sleeveless	XL	Dresses			2XP	2XP	T-Shirt	Tops
8	Old Navy Active 5.5" Shorts Womens 3X Plus Olive Camo Stretch Tech Zip Pocket	3X				1XP	1XP	Shirt	Tops
9	Allison Daley T-Shirt Blouse Womens 1X Polyester Spandex Short Sleeve Colorful	1X	Tops			0XP	0XP	Tank	Tops
10	Old Navy NWT Wide Linen Blend Pants Womens Large Flax Beige Pockets High Rise	L	Pants			3XWP	3XP	Dress	Dresses
11	Apt 9 Kohls Snake Print Top Shirt Blouse Cotton Stretch Womens Plus 1X	1X	Tops			2XWP	2XP	Shirt Dress	Dresses
12	American Eagle Mom Short Womens 6 High Rise Zip Distressed Denim 2.5" Inseam	6				1XWP	1XP	Jumpsuit	Jumpsuits
13	D&Co Active Sleeveless 1/4 Zip Shirt Womens 1X Blue Cotton Spandex Tank Golf	1X	Tops			0XWP	0XP	Romper	Jumpsuits
14	Croft & Barrow The Classic Tee Womens 1X Cotton Black Polka Dot T-Shirt Crew SS	1X	Tops			26P	26P	Overalls	Jumpsuits
15	Old Navy 100% Cashmere Sweater Full Zip Hoodie Womens XL (S, M) Long Sleeve	XL				24P	24P	Jeans	Jeans
16	We The Free People Denim Cut-off Shorts Womens 26 2" Blue Cotton Fringe	26				22P	22P	Pants	Pants
17	Melissa Paige NWT Petite Elastic Waist Skirt Blue Polyester Lined Womens PXL	PXL				20P	20P	Leggings	Pants
18	Nike Dri Fit Shirt Womens Large Relaxed Long Sleeve Teal Heather Crew Raglan	L	Tops			18P	18P	Jacket	Jackets
19	Nike Performance Polo Shirt Mens 2XL XXL Red Striped Short Sleeve Polyester	2XL	Tops			16P	16P	Vest	Jackets
20	JMS Just My Size NWT Blouse Polo Womens 2X 18W 20W Yellow 3/4 Sleeve Cotton	2X	Tops			14P	14P	Sport Coat	Sport Coats
21	Old Navy Boyfriend Mid Rise Blue Denim Shorts Womens 22 Cotton Stretch 2.5" Inse	22				12P	12P	Blazer	Sport Coats
22	Nike Golf Shorts Pleated Beige Polyester 10" Mens 33	33				10P	10P	Dress Shirt	Tops
23	Croft & Barrow Blouse Womens 2X Rayon Spandex Black Ruched Cuffs Pleated	2X	Tops			8P	8P
24	Woman Within Dress Womens 3X 30/32 Rayon Black Short Sleeve Floral	3X	Dresses			6P	6P
25	Sonoma T-Shirt Womens 2XL XXL Yellow Paisley Print Cotton Polyester Short Sleeve	2XL	Tops			4P	4P
26	Athleta Salutation Stash Pocket II 7" Short Womens 2X Blue Nylon Lycra Spandex	2X				2P	2P
27	NYDJ NWT Capri Jeans Original Slimming Fit Womens 24W Denim Stretch	24W	Jeans			0P	0P
28	Swannies Polo Shirt Mens Medium Blue Polyester Spandex Short Sleeve Striped	M	Tops			00P	00P
29	Levis 511 Denim Shorts Mens 30 Cotton Blue 11" Inseam Slim	30				P7XL	P7XL
30	Fila Sport NWT Golf Polo Shirt Mens 4XB 4X Big Gray Athletic Fit Polyester Wick	4XB	Tops			P6XL	P6XL
31	Under Armour T-Shirt Womens 2XL XXL Pink Threadborne Loose Fit HeatGear V-Neck	2XL	Tops			P5XL	P5XL
32	Lucky Brand Blouse Womens Medium Blue V-Neck Short Sleeve Elastic Waist	M	Tops			P4XL	P4XL
33	Perceptions Dress Womens 1X Floral Black Yellow Sleeveless A-Line Swing Stretch	1X	Dresses			P3XL	P3XL
34	Red Hot By Spanx Full Slip Womens Large Spandex Nylon Soft Nude Lace Slimming	L				P2XL	P2XL
35	Knox Rose Kimono Blouse Womens M/L Rayon Open Front Floral Red Stain	L	Tops			PXL	PXL
36	Susan Graver Ultra Stretch Pull On Pedal Pushers With Pockets Womens XL	XL				XLP	PXL
37	Suzanne Betro NWT Shirred Fit & Flare Dress Black/White Crew Stretch Womens M	M	Dresses			PL	PL
38	Tommy Bahama Orange Striped Polo Shirt Polyester Mens Medium M	M	Tops			LP	PL
39	Champion Tank Top Mens 5XLT Gray Spell out Cotton Blend T-Shirt	5XLT	Tops			PM	PM
40	Outdoor Research NWT T-Shirt Mens Large Echo Duo Tee Pewter Alloy Gray Polyester	L	Tops			MP	PM
41	Chaps Blouse Womens 3X Cotton Blend Beach Palm Trees Short Sleeve Blue	3X	Tops			SP	PS
42	White Stag Sport Collection Blouse Womens 22W 2X Blue Polyester Cotton	2X	Tops			PS	PS
43	Love + Joy Blouse Womens 3X Yellow Floral V-Neck Short Sleeve Elastic Waist	3X	Tops			PXS	PXS
44	Quacker Factory Snap Front Cardigan Womens Medium Yellow Floral Long Sleeve	M				P2XS	P2XS
45	Tahari 100% Cashmere Sweater Womens Small Beige Pockets Luxe Long Sleeve	S				7XB	7XB
46	Style & Co Woman Embellished V-Neck Blue/Green Top Blouse Nylon Womens 2X	2X	Tops			6XB	6XB
47	Carhartt Polo Shirt Mens 2XLT Tall Green Pocket Short Sleeve Loose Fit	2XLT	Tops			5XB	5XB
48	Maurices Cardigan Sweater Womens 2X Open Front Ruffle Dolman Rayon Blend	2X				4XB	4XB
49	Wilfred Merino Wool Sweater Womens Medium Brown Mock Long Sleeve	M				3XB	3XB
50	Duluth Trading Shirt Mens XL Trim Fit Multicolor Check Plaid Short Sleeve Button	XL	Tops			2XB	2XB
51	Old Navy Sweater Pullover Womens 2XL XXL Cotton Blend Classic Crew Gray	2XL				1XB	1XB
52	Croft & Barrow Button Front Shirt Womens 3X Cotton White Flamingo Pockets Stain	3X	Tops			6XLT	6XLT
53	Old Navy Mens XL Navy/Gray Gingham Plaid Shirt Long Sleeve Built In Flex Button	XL	Tops			5XLT	5XLT
54	Arcteryx T-Shirt Mens Medium M Gray Law Enforcement Armed Forces Crew	M	Tops			4XLT	4XLT
55	Falls Creek Dress Womens 2X Cotton Spandex Short Sleeve V-Neck Fit Flare Red	2X	Dresses			3XLT	3XLT
56	Maurices Tank Top Blouse Womens 2X Rayon Ruffle Sleeveless Red	2X	Tops			2XLT	2XLT
57	Knox Rose Tank Top Blouse Womens Large Blue White Floral Sleeveless Rayon Cami	L	Tops			XLT	XLT
58	Torrid Button Front Shirt Womens 2 2X Blue Tencel Chambray Sleeveless Dolman	2X	Tops			LT	LT
59	Lands End No Iron Pinpoint Oxford Shirt Womens 14 Long Sleeve Cotton Floral	14	Tops			MT	MT
60	Ellen Tracy Linen Pants Womens XL Navy Blue Pull On Elastic Crop Capri Pockets	XL	Pants			7XL	7XL
61	Lands End Sport Tank Top Blouse Blue Stretch Womens 2X	2X	Tops			6XL	6XL
62	J Crew Pull On Pants Womens 10 Linen Cotton Striped Gray White Drawstring Pocket	10	Pants			5XL	5XL
63	Torrid Cropped Cami Linen Blend Top Womens 1 1X Blue Stretch Smocked	1X	Tops			4XL	4XL
64	7 For All Mankind Luxe Performance The Straight Mens Jeans 36X31 Stretch	36	Jeans			3XL	3XL
65	American Eagle Vintage 7" Shorts Womens 14 Cotton Denim Blue Cut Off	14				2XL	2XL
66	Levis 525 Straight Perfect Waist Jeans Womens 6S Cotton Spandex Original	6	Jeans			XL	XL
67	Shein T-Shirt White Tee Crew Short Sleeve Flag/Sunflower Stretch Womens XL	XL	Tops			1XL	XL
68	Eddie Bauer Adventurer 2.0 Skort Womens 8 Green Polyester Spandex Short Liner	8				L	L
69	J Crew Draped Pocket Tank Tunic Top Polyester Sleeveless Womens Size 4	4	Tops			M	M
70	Levis Premium Big E Wedgie Jeans Womens 30 Cotton Stretch Distressed Blue	30	Jeans			S	S
71	Old Navy Boyfriend Flannel Shirt Womens 2XL XXL Red Plaid Long Sleeve Cotton	2XL	Tops			XS	XS
72	JM Collection Round Neck Beaded Blouse Top Shirt Cotton Modal Womens 3X	3X	Tops			2XS	2XS
73	Karen Scott Pullover Sweater Womens 2X Red Cotton Raglan Crew Knit	2X				XXS	2XS
74	Catherines Dress Womens 4X Short Sleeve Red Tiered Shift Lined Polyester 32W 30W	4X	Dresses			7X	7X
75	Lands End Polo Shirt Womens 2X 20W 22W Red Polka Dot Cotton Spandex Short Sleeve	2X	Tops			6X	6X
76	Terra & Sky 4" Shorts Womens 2X 20W 22W Tie Dye Drawstring Elastic Waist Soft	2X				5X	5X
77	I Love Jesus And Naps T-Shirt Womens 18 20 2X 2 Elly And Grace Live And Tell NWT	2X	Tops			4X	4X
78	Sara Michelle Blouse Womens 2X Polyester Green Tiered Lined Short Sleeve	2X	Tops			3X	3X
79	American Eagle Womens 6 Vintage Hi Rise Festival Distressed Gray Shorts Denim	6				2X	2X
80	C Est 1946 Cato Blouse Womens 22/24W 3X Floral Polyester Spandex	3X	Tops			1X	1X
81	JM Collection Linen Shirt Womens 24W 3X Button Front Pocket Floral Tab Sleeve	3X	Tops			0X	0X
82	Maeve Womens Sz 4 Scallop Swiss Dot Blouse Top Blue Navy Rayon Crew Neck	4	Tops			XXXL	3XL
83	Terra & Sky Sheer Blouse Button Front Black Floral Long Sleeve Womens 3X 24W 26W	3X	Tops			XXL	2XL
84	Catherines Suprema Collection T-Shirt Womens 1X 18/20W Green Cotton Spandex V	1X	Tops			Large	L
85	Uniqlo Button Front Shirt Womens 2XL XXL Poly/Rayon Long Sleeve Flaw	2XL	Tops			Medium	M
86	River Trader Button Front Shirt Mens 2XL XXL Seer****** Cotton Pocket SS Yellow	2XL	Tops			Small	S
87	Chaps Blouse Womens 2X Polyester 3/4 Sleeve All Over Floral Print	2X	Tops			44W	44W
88	Cato Blouse Womens 18/20W 2X Brown V-Neck Short Sleeve Elastic Waist	2X	Tops			42W	42W
89	Eliane Rose Blouse Tank Womens 1X Plus White Teal Brown Viscose Spandex Tunic	1X	Tops			38W	38W
90	LL Bean Chamois Cloth Shirt Mens Large Regular Red Pockets Long Sleeve Cotton	L	Tops			36W	36W
91	Croft & Barrow Light Jacket Womens 1X Green Paisley Full Zip Polyester	1X	Jackets			34W	34W
92	Banana Republic Pants Mens 34X28 Linen Cotton Straight Fit Beige Plaid Pro Hem	34	Pants			32W	32W
93	Shein Curve Fit Flare Dress Womens 2XL Pink Hearts V Wrap A-Line Short Sleeve	2XL	Dresses			30W	30W
94	American Eagle Hi Rise Festival Shorts Womens 4 Cotton Distressed Blue Denim Zip	4				28W	28W
95	Protege Collection Cosby Sweater Mens XLT Made USA Black Crew Pullover	XLT				26W	26W
96	Belle Sky Blouse Womens 2X Blue Tie Neck Long Sleeve Polyester Ruffle	2X	Tops			24W	24W
97	White Stag NWT Draped Tankini Spandex Black Halter Womens 3X 22W 24W	3X				22W	22W
98	Boden Dress Womens 6P Cotton Spandex Short Sleeve Empire Tulip Jersey Gray	6P	Dresses			20W	20W
99	Old Navy Active Powersoft Leggings Womens Large Go Dry High Rise Gray Heather	L	Pants			18W	18W
100	Chicos Weekends Blouse Tunic Womens 1 (US Medium) 8 Green Floral 3/4 Sleeve	M	Tops			16W	16W
101	Old Navy Button Front Shirt Mens 2XL XXL Blue Slim Built In Flex Pocket SS	2XL	Tops			14W	14W
102	The North Face Narrows Short Mens 34 Shorts Distressed Straight Twill 8" Inseam	34				One Size	One Size
103	Terra & Sky Blouse Womens 1X 16W 18W Cotton Short Sleeve Notch Neck Swing Orange	1X	Tops			All Sizes	One Size
104	LL Bean Button Front Shirt Womens Medium Reg Pink Stripe Long Sleeve Stain	M	Tops			60	60
105	Cato Blouse Womens 18W 20W Brown Paisley Split Round Hem 3/4 Sleeve V-Neck 2X	2X	Tops			58	58
106	Adidas Leggings Womens 2X Primegreen Aeroready Pink Abstract	2X	Pants			56	56
107	Sag Harbor Button Front Shirt Womens 1X Polyester Lined Short Sleeve Orange V	1X	Tops			54	54
108	Alice + Olivia Blouse Top Womens XS Stretchy Cropped Snake Print Long Sleeve	XS	Tops			52	52
109	LOFT Ann Taylor Blouse Womens XL Yellow 3/4 Sleeve Cotton	XL	Tops			50	50
110	Terra & Sky Button Front Shirt Womens 1X 16W 18W Blue Stripe Pockets SS	1X	Tops			48	48
111	Torrid Jogger Pants Womens 3 3X Green Cargo Rayon Stretch Pull On Elastic	3X	Pants			46	46
112	Guess Linen Pants Cropped Capri Black Drawstring Womens 32	32	Pants			44	44
113	Eddie Bauer T-Shirt Mens 2XL XXL Cotton Black Long Sleeve Mock	2XL	Tops			42	42
114	Old Navy NWT Slim Jeans Mens 38X31 Black Wash Cotton 360 Stretch Zip Mid	38	Jeans			40	40
115	Muk Luks Womens Cabin Sock NWT Black L/XL 8-10 Slip Resistant Warm Cozy	XL				38	38
116	George Long Sleeve Button Front Shirt Mens 2XL XXL Blue Pink Check Plaid Cotton	2XL	Tops			36	36
117	Lands End Tunic Blouse Womens XL 18 Gray Pockets Long Sleeve V-Neck Cotton Blend	XL	Tops			35	35
118	CJ Banks Button Front Blouse Womens 2X Linen Rayon Black Pockets Jacket	2X	Tops			34	34
119	Talbots Pants Womens XLP Petite Plaid Black Blue Spandex Leggings Pull On	PXL	Pants			33	33
120	Blair Pull On Capri Pants 24W Teal Pockets Elastic Waist Cotton Polyester	24W	Pants			32	32
121	Eddie Bauer Parka Coat Womens 1X Orange Cotton Polyester Hooded	1X				31	31
122	Levis Denim Shirt Mens XL Cotton Long Sleeve Pockets Charcoal Wash Button Up	XL	Tops			30	30
123	Levis 502 Taper Fit Mens Jeans Big E Premium (Actual 33X28) 33X30 Cotton Spandex	33	Jeans			29	29
124	Lucky Brand Button Front Shirt Mens Large Linen Orange/Red/Yellow Plaid SS	L	Tops			28	28
125	Paradise Found Made In Hawaii Hawaiian Shirt Rayon Camp Mens Large L	L	Tops			27	27
126	Lucky Brand Henley Blouse Womens Large Taupe Short Sleeve Dolman Pockets	L	Tops			26	26
127	Eddie Bauer Polo Shirt Mens 2XL XXL Orange Pima Cotton Cocona Polyester SS	2XL	Tops			25	25
128	Verve Ami Cardigan Sweater Womens 3X Rayon Blend Red Open Front Long Sleeve	3X				24	24
129	J Crew Mercantile Slim Flex Shirt Mens Large Cotton Spandex Plaid Long Sleeve	L	Tops			22	22
130	Grayson The Hero Button Up Shirt Womens Size 3 (M) Bicycle White Red Long Sleeve	M	Tops			20	20
131	Croft & Barrow Tunic Blouse Womens 1X Rayon Blue Floral Smocked 3/4 Sleeve	1X	Tops			18	18
132	Livi Dress Womens 18/20 2X Blue Tie Dye Rayon Spandex Maxi Sleeveless	2X	Dresses			16	16
133	JMS Just My Size Blouse Womens 2X 18W 20W Polyester Flare Sleeve Purple Pink	2X	Tops			14	14
134	Great Northwest Indigo Blouse Womens 2X White Embroidered Short Sleeve	2X	Tops			12	12
135	C Est 1946 Cato Blouse Womens 26/28W 4X Yellow Pullover Puff Sleeve 3/4	4X	Tops			10	10
136	Eileen Fisher Jumpsuit Womens Large Black Rayon Spandex Pockets Wide Leg Zip	L	Jumpsuits			0	0
137	Nike Dri Fit Running Shorts Womens 2X Brief Lined Polyester Black Teal	2X				8	8
138	Bonobos Button Front Shirt Mens XL Short Blue Cotton Seer****** Standard Fit	XL	Tops			6	6
139	Chicos Maxi Dress Womens 0 (US Small, 4) Sleeveless Polyester Spandex Lined	S	Dresses			4	4
140	Avenue Shirt Womens 22/24 Purple Crinkle Long Sleeve Button Front Top	24	Tops			2	2
141	JM Collection Button Front Shirt Womens 24W 3X Cotton Purple Floral Sequin V	3X	Tops			0	0
142	Cato Button Front Shirt Womens 18/20W 2X Orange Floral Cotton Short Sleeve	2X	Tops
143	Lane Bryant Womens 24 Button Front Shirt Tie Wrap Belt Blue Cotton Stretch	24	Tops
Sheet2

 

[Akuini]

More than 10,000 rows. I create about 3500 listing per year and I expect to double or triple that next year. The data comes in on CSV reports and then I transform the data via Power Query.

The code uses Instr function and Regex to find match per criteria. Regex is more robust & powerful but slower than Instr. Therefore I use Instr in most part of the code and use Regex in parts that I think Instr will fail to do the job. With your large data set I think it's the best choice.

Please, test the code on actual data & post the result you get from Immediate window.
Using the above example, the result is:
Number of records : : 142
Number of Size List : : 140
Number of Category List : : 21
Completion time: 0,03 seconds

 

[frewert]

Size project method D.xlsm
	A	B	C	D	E	F	G	H	I
1	Item Title	Size				List of valid size entries	replace entries	Category
2	Lands End Denim Shirt Womens M 10/12 Cotton Vintage Long Sleeve Button Up	M	Tops			7XP	7XP	Top	Tops
3	Lululemon 11" Shorts Mens XL Black Zip Pockets Elastic Waist Drawstring Vintage	XL	Shorts			6XP	6XP	Blouse	Tops
4	LC Lauren Conrad T-Shirt Womens 2XL XXL Plus Crew Short Sleeve Cotton Floral	2XL	Tops			5XP	5XP	Tee	Tops
5	Dickies Long Sleeve Pocket Tee Mens 3XL XXXL Orange Relaxed Performance Wicking	3XL	Tops			4XP	4XP	Tunic	Tops
6	Croft & Barrow Blouse Womens 2X Blue 3/4 Sleeve Knit Spandex	2X	Tops			3XP	3XP	Kimono	Tops
7	Chicos Travelers Dress Womens 3 (US: XL) Black Acetate Spandex Sheath Sleeveless	XL	Dresses			2XP	2XP	T-Shirt	Tops
8	Old Navy Active 5.5" Shorts Womens 3X Plus Olive Camo Stretch Tech Zip Pocket	3X	Shorts			1XP	1XP	Shirt	Tops
9	Allison Daley T-Shirt Blouse Womens 1X Polyester Spandex Short Sleeve Colorful	1X	Tops			0XP	0XP	Tank	Tops
10	Old Navy NWT Wide Linen Blend Pants Womens Large Flax Beige Pockets High Rise	L	Pants			3XWP	3XP	Dress	Dresses
11	Apt 9 Kohls Snake Print Top Shirt Blouse Cotton Stretch Womens Plus 1X	1X	Tops			2XWP	2XP	Shirt Dress	Dresses
12	American Eagle Mom Short Womens 6 High Rise Zip Distressed Denim 2.5" Inseam	6				1XWP	1XP	Jumpsuit	Jumpsuits
13	D&Co Active Sleeveless 1/4 Zip Shirt Womens 1X Blue Cotton Spandex Tank Golf	1X	Tops			0XWP	0XP	Romper	Jumpsuits
14	Croft & Barrow The Classic Tee Womens 1X Cotton Black Polka Dot T-Shirt Crew SS	1X	Tops			26P	26P	Overalls	Jumpsuits
15	Old Navy 100% Cashmere Sweater Full Zip Hoodie Womens XL (S, M) Long Sleeve	XL	Sweaters			24P	24P	Jeans	Jeans
16	We The Free People Denim Cut-off Shorts Womens 26 2" Blue Cotton Fringe	26	Shorts			22P	22P	Pants	Pants
17	Melissa Paige NWT Petite Elastic Waist Skirt Blue Polyester Lined Womens PXL	PXL	Skirts			20P	20P	Leggings	Pants
18	Nike Dri Fit Shirt Womens Large Relaxed Long Sleeve Teal Heather Crew Raglan	L	Tops			18P	18P	Jacket	Jackets
19	Nike Performance Polo Shirt Mens 2XL XXL Red Striped Short Sleeve Polyester	2XL	Tops			16P	16P	Vest	Jackets
20	JMS Just My Size NWT Blouse Polo Womens 2X 18W 20W Yellow 3/4 Sleeve Cotton	2X	Tops			14P	14P	Sport Coat	Sport Coats
21	Old Navy Boyfriend Mid Rise Blue Denim Shorts Womens 22 Cotton Stretch 2.5" Inse	22	Shorts			12P	12P	Blazer	Sport Coats
22	Nike Golf Shorts Pleated Beige Polyester 10" Mens 33	33	Shorts			10P	10P	Suit Jacket	Sport Coats
23	Croft & Barrow Blouse Womens 2X Rayon Spandex Black Ruched Cuffs Pleated	2X	Tops			8P	8P	Waist Coat	Sport Coats
24	Woman Within Dress Womens 3X 30/32 Rayon Black Short Sleeve Floral	3X	Dresses			6P	6P	Sweater	Sweaters
25	Sonoma T-Shirt Womens 2XL XXL Yellow Paisley Print Cotton Polyester Short Sleeve	2XL	Tops			4P	4P	Cardigan	Sweaters
26	Athleta Salutation Stash Pocket II 7" Short Womens 2X Blue Nylon Lycra Spandex	2X				2P	2P	Shorts	Shorts
27	NYDJ NWT Capri Jeans Original Slimming Fit Womens 24W Denim Stretch	24W	Jeans			0P	0P	Skirt	Skirts
28	Swannies Polo Shirt Mens Medium Blue Polyester Spandex Short Sleeve Striped	M	Tops			00P	00P	Skort	Skirts
29	Levis 511 Denim Shorts Mens 30 Cotton Blue 11" Inseam Slim	30	Shorts			P7XL	P7XL	Polo	Polos
30	Fila Sport NWT Golf Polo Shirt Mens 4XB 4X Big Gray Athletic Fit Polyester Wick	4XB	Tops			P6XL	P6XL	Rugby Shirt	Polos
31	Under Armour T-Shirt Womens 2XL XXL Pink Threadborne Loose Fit HeatGear V-Neck	2XL	Tops			P5XL	P5XL	Hoodie	Hoodies/Sweatshirts
32	Lucky Brand Blouse Womens Medium Blue V-Neck Short Sleeve Elastic Waist	M	Tops			P4XL	P4XL	Sweatshirt	Hoodies/Sweatshirts
33	Perceptions Dress Womens 1X Floral Black Yellow Sleeveless A-Line Swing Stretch	1X	Dresses			P3XL	P3XL	Hat	Misc
34	Red Hot By Spanx Full Slip Womens Large Spandex Nylon Soft Nude Lace Slimming	L				P2XL	P2XL	Wallet	Misc
35	Knox Rose Kimono Blouse Womens M/L Rayon Open Front Floral Red Stain	L	Tops			PXL	PXL	Bag	Misc
36	Susan Graver Ultra Stretch Pull On Pedal Pushers With Pockets Womens XL	XL				XLP	PXL	Scarf	Misc
37	Suzanne Betro NWT Shirred Fit & Flare Dress Black/White Crew Stretch Womens M	M	Dresses			PL	PL
38	Tommy Bahama Orange Striped Polo Shirt Polyester Mens Medium M	M	Tops			LP	PL
39	Champion Tank Top Mens 5XLT Gray Spell out Cotton Blend T-Shirt	5XLT	Tops			PM	PM
40	Outdoor Research NWT T-Shirt Mens Large Echo Duo Tee Pewter Alloy Gray Polyester	L	Tops			MP	PM
41	Chaps Blouse Womens 3X Cotton Blend Beach Palm Trees Short Sleeve Blue	3X	Tops			SP	PS
42	White Stag Sport Collection Blouse Womens 22W 2X Blue Polyester Cotton	2X	Tops			PS	PS
43	Love + Joy Blouse Womens 3X Yellow Floral V-Neck Short Sleeve Elastic Waist	3X	Tops			PXS	PXS
44	Quacker Factory Snap Front Cardigan Womens Medium Yellow Floral Long Sleeve	M	Sweaters			P2XS	P2XS
45	Tahari 100% Cashmere Sweater Womens Small Beige Pockets Luxe Long Sleeve	S	Sweaters			7XB	7XB
46	Style & Co Woman Embellished V-Neck Blue/Green Top Blouse Nylon Womens 2X	2X	Tops			6XB	6XB
47	Carhartt Polo Shirt Mens 2XLT Tall Green Pocket Short Sleeve Loose Fit	2XLT	Tops			5XB	5XB
48	Maurices Cardigan Sweater Womens 2X Open Front Ruffle Dolman Rayon Blend	2X	Sweaters			4XB	4XB
49	Wilfred Merino Wool Sweater Womens Medium Brown Mock Long Sleeve	M	Sweaters			3XB	3XB
50	Duluth Trading Shirt Mens XL Trim Fit Multicolor Check Plaid Short Sleeve Button	XL	Tops			2XB	2XB
51	Old Navy Sweater Pullover Womens 2XL XXL Cotton Blend Classic Crew Gray	2XL	Sweaters			1XB	1XB
52	Croft & Barrow Button Front Shirt Womens 3X Cotton White Flamingo Pockets Stain	3X	Tops			6XLT	6XLT
53	Old Navy Mens XL Navy/Gray Gingham Plaid Shirt Long Sleeve Built In Flex Button	XL	Tops			5XLT	5XLT
54	Arcteryx T-Shirt Mens Medium M Gray Law Enforcement Armed Forces Crew	M	Tops			4XLT	4XLT
55	Falls Creek Dress Womens 2X Cotton Spandex Short Sleeve V-Neck Fit Flare Red	2X	Dresses			3XLT	3XLT
56	Maurices Tank Top Blouse Womens 2X Rayon Ruffle Sleeveless Red	2X	Tops			2XLT	2XLT
57	Knox Rose Tank Top Blouse Womens Large Blue White Floral Sleeveless Rayon Cami	L	Tops			XLT	XLT
58	Torrid Button Front Shirt Womens 2 2X Blue Tencel Chambray Sleeveless Dolman	2X	Tops			LT	LT
59	Lands End No Iron Pinpoint Oxford Shirt Womens 14 Long Sleeve Cotton Floral	14	Tops			MT	MT
60	Ellen Tracy Linen Pants Womens XL Navy Blue Pull On Elastic Crop Capri Pockets	XL	Pants			7XL	7XL
61	Lands End Sport Tank Top Blouse Blue Stretch Womens 2X	2X	Tops			6XL	6XL
62	J Crew Pull On Pants Womens 10 Linen Cotton Striped Gray White Drawstring Pocket	10	Pants			5XL	5XL
63	Torrid Cropped Cami Linen Blend Top Womens 1 1X Blue Stretch Smocked	1X	Tops			4XL	4XL
64	7 For All Mankind Luxe Performance The Straight Mens Jeans 36X31 Stretch	36	Jeans			3XL	3XL
65	American Eagle Vintage 7" Shorts Womens 14 Cotton Denim Blue Cut Off	14	Shorts			2XL	2XL
66	Levis 525 Straight Perfect Waist Jeans Womens 6S Cotton Spandex Original	6	Jeans			XL	XL
67	Shein T-Shirt White Tee Crew Short Sleeve Flag/Sunflower Stretch Womens XL	XL	Tops			1XL	XL
68	Eddie Bauer Adventurer 2.0 Skort Womens 8 Green Polyester Spandex Short Liner	8	Skirts			L	L
69	J Crew Draped Pocket Tank Tunic Top Polyester Sleeveless Womens Size 4	4	Tops			M	M
70	Levis Premium Big E Wedgie Jeans Womens 30 Cotton Stretch Distressed Blue	30	Jeans			S	S
71	Old Navy Boyfriend Flannel Shirt Womens 2XL XXL Red Plaid Long Sleeve Cotton	2XL	Tops			XS	XS
72	JM Collection Round Neck Beaded Blouse Top Shirt Cotton Modal Womens 3X	3X	Tops			2XS	2XS
73	Karen Scott Pullover Sweater Womens 2X Red Cotton Raglan Crew Knit	2X	Sweaters			XXS	2XS
74	Catherines Dress Womens 4X Short Sleeve Red Tiered Shift Lined Polyester 32W 30W	4X	Dresses			7X	7X
75	Lands End Polo Shirt Womens 2X 20W 22W Red Polka Dot Cotton Spandex Short Sleeve	2X	Tops			6X	6X
76	Terra & Sky 4" Shorts Womens 2X 20W 22W Tie Dye Drawstring Elastic Waist Soft	2X	Shorts			5X	5X
77	I Love Jesus And Naps T-Shirt Womens 18 20 2X 2 Elly And Grace Live And Tell NWT	2X	Tops			4X	4X
78	Sara Michelle Blouse Womens 2X Polyester Green Tiered Lined Short Sleeve	2X	Tops			3X	3X
79	American Eagle Womens 6 Vintage Hi Rise Festival Distressed Gray Shorts Denim	6	Shorts			2X	2X
80	C Est 1946 Cato Blouse Womens 22/24W 3X Floral Polyester Spandex	3X	Tops			1X	1X
81	JM Collection Linen Shirt Womens 24W 3X Button Front Pocket Floral Tab Sleeve	3X	Tops			0X	0X
82	Maeve Womens Sz 4 Scallop Swiss Dot Blouse Top Blue Navy Rayon Crew Neck	4	Tops			XXXL	3XL
83	Terra & Sky Sheer Blouse Button Front Black Floral Long Sleeve Womens 3X 24W 26W	3X	Tops			XXL	2XL
84	Catherines Suprema Collection T-Shirt Womens 1X 18/20W Green Cotton Spandex V	1X	Tops			Large	L
85	Uniqlo Button Front Shirt Womens 2XL XXL Poly/Rayon Long Sleeve Flaw	2XL	Tops			Medium	M
86	River Trader Button Front Shirt Mens 2XL XXL Seer****** Cotton Pocket SS Yellow	2XL	Tops			Small	S
87	Chaps Blouse Womens 2X Polyester 3/4 Sleeve All Over Floral Print	2X	Tops			44W	44W
88	Cato Blouse Womens 18/20W 2X Brown V-Neck Short Sleeve Elastic Waist	2X	Tops			42W	42W
89	Eliane Rose Blouse Tank Womens 1X Plus White Teal Brown Viscose Spandex Tunic	1X	Tops			38W	38W
90	LL Bean Chamois Cloth Shirt Mens Large Regular Red Pockets Long Sleeve Cotton	L	Tops			36W	36W
91	Croft & Barrow Light Jacket Womens 1X Green Paisley Full Zip Polyester	1X	Jackets			34W	34W
92	Banana Republic Pants Mens 34X28 Linen Cotton Straight Fit Beige Plaid Pro Hem	34	Pants			32W	32W
93	Shein Curve Fit Flare Dress Womens 2XL Pink Hearts V Wrap A-Line Short Sleeve	2XL	Dresses			30W	30W
94	American Eagle Hi Rise Festival Shorts Womens 4 Cotton Distressed Blue Denim Zip	4	Shorts			28W	28W
95	Protege Collection Cosby Sweater Mens XLT Made USA Black Crew Pullover	XLT	Sweaters			26W	26W
96	Belle Sky Blouse Womens 2X Blue Tie Neck Long Sleeve Polyester Ruffle	2X	Tops			24W	24W
97	White Stag NWT Draped Tankini Spandex Black Halter Womens 3X 22W 24W	3X				22W	22W
98	Boden Dress Womens 6P Cotton Spandex Short Sleeve Empire Tulip Jersey Gray	6P	Dresses			20W	20W
99	Old Navy Active Powersoft Leggings Womens Large Go Dry High Rise Gray Heather	L	Pants			18W	18W
100	Chicos Weekends Blouse Tunic Womens 1 (US Medium) 8 Green Floral 3/4 Sleeve	M	Tops			16W	16W
Sheet1

Number of records : : 1286
Number of Size List : : 140
Number of Category List : : 35
Completion time: 0.10 seconds

Very good results. Just a few things to clean up.

• Short should be used for the Shorts category but a problem exists because many Tops titles are described as "Short Sleeve".
• Full Slip is an Intimates category item. A very low percentage of my business, maybe .01%
• Tankini is a Swimsuit category. Another very low percentage of my business. But of course, painless to add that to the column.
• Pedal Pushers is a rare wording but they would be in Pants category.

I will look at more errors tomorrow past 100 rows. Thanks for your help tonight as always!

 

[Akuini]

Short should be used for the Shorts category but a problem exists because many Tops titles are described as "Short Sleeve".

There is Shorts but no Short in col H, you should put Short on col H. So, it's not because there is Short Sleeve in Title.

 

[frewert]

I've added several things to the Category column.
Not sure why the ones with 3XWP, Torrid sizes, All Sizes, and Bag didn't work right.
There is some error with Short being read over Top, resulting in the wrong category. (But many cases (203 total) where it did work correctly)

Item Title	Size	Category	Note	Note 2
American Eagle Curvy Hi Rise Jegging Womens 10 X-Long (32 Inseam) Black Stretch	32	Jeans	Should be 10	Maybe ignore "(number) Inseam"
Torrid Maxi Dress Womens Size 1 Semi Sheer Polyester Short Sleeve Elastic Waist		Dresses	Should be 1X
Torrid T-Shirt Womens 3 V-Neck Pocket Tee Pink Striped Short Sleeve Cotton Blend		Tops	Should be 3X
Catherines Suprema Collection Blouse Womens 3XWP Teal Short Sleeve Cotton V		Tops	Should be 3XWP
Polo Ralph Lauren Vintage Bag Blackwatch Leather Shoulder Strap Zip		Polos	Should be Misc
Handmade In Nepal 100% Cashmere Sweater Fits All Sizes Brown Poncho		Sweaters	Should be One Size
Maurices Top Womens 0X Black Floral Rayon Stretch Short Sleeve Flutter Pink	0X	Shorts	Should be Tops
Old Navy NWT Top Womens XL Ribbed Ultralite Go Dry Short Sleeve Crop Tee	XL	Shorts	Should be Tops
LOFT Ann Taylor Wrap Top Womens Large Blue Lyocell Chambray Short Sleeve V	L	Shorts	Should be Tops
J Crew Flutter Sleeve Wrap Top Womens 6 Pink Tie Short Sleeve Cotton Side Zip	6	Shorts	Should be Tops
Truself Knit Top Womens 2X Metallic Lettering Beige Soft Short Sleeve Dolman	2X	Shorts	Should be Tops
Liz Baker Button Front Top Womens 18W Plus White/Black Short Sleeve Polyester	18W	Shorts	Should be Tops
Perch By Blu Pepper Womens Top 2XL XXL Beige Polyester Tie Bow Short Sleeve	2XL	Shorts	Should be Tops
Bobbie Brooks Henley Top Womens 2X White Lace Back Short Sleeve Stretch	2X	Shorts	Should be Tops

Items that were the correct Tops category but had "Short" in the title (203 total).

Item Title	Size	Category
LC Lauren Conrad T-Shirt Womens 2XL XXL Plus Crew Short Sleeve Cotton Floral	2XL	Tops
Allison Daley T-Shirt Blouse Womens 1X Polyester Spandex Short Sleeve Colorful	1X	Tops
Nike Performance Polo Shirt Mens 2XL XXL Red Striped Short Sleeve Polyester	2XL	Tops
Sonoma T-Shirt Womens 2XL XXL Yellow Paisley Print Cotton Polyester Short Sleeve	2XL	Tops
Swannies Polo Shirt Mens Medium Blue Polyester Spandex Short Sleeve Striped	M	Tops
Lucky Brand Blouse Womens Medium Blue V-Neck Short Sleeve Elastic Waist	M	Tops
Chaps Blouse Womens 3X Cotton Blend Beach Palm Trees Short Sleeve Blue	3X	Tops
Love + Joy Blouse Womens 3X Yellow Floral V-Neck Short Sleeve Elastic Waist	3X	Tops
Carhartt Polo Shirt Mens 2XLT Tall Green Pocket Short Sleeve Loose Fit	2XLT	Tops
Duluth Trading Shirt Mens XL Trim Fit Multicolor Check Plaid Short Sleeve Button	XL	Tops
Shein T-Shirt White Tee Crew Short Sleeve Flag/Sunflower Stretch Womens XL	XL	Tops
Lands End Polo Shirt Womens 2X 20W 22W Red Polka Dot Cotton Spandex Short Sleeve	2X	Tops
Sara Michelle Blouse Womens 2X Polyester Green Tiered Lined Short Sleeve	2X	Tops
Cato Blouse Womens 18/20W 2X Brown V-Neck Short Sleeve Elastic Waist	2X	Tops
Terra & Sky Blouse Womens 1X 16W 18W Cotton Short Sleeve Notch Neck Swing Orange	1X	Tops
Sag Harbor Button Front Shirt Womens 1X Polyester Lined Short Sleeve Orange V	1X	Tops
Lucky Brand Henley Blouse Womens Large Taupe Short Sleeve Dolman Pockets	L	Tops
Great Northwest Indigo Blouse Womens 2X White Embroidered Short Sleeve	2X	Tops
Bonobos Button Front Shirt Mens XL Short Blue Cotton Seer****** Standard Fit	XL	Tops
Cato Button Front Shirt Womens 18/20W 2X Orange Floral Cotton Short Sleeve	2X	Tops
Cato Tunic Blouse Womens 22/24W 3X Cotton Short Sleeve Brown Pink Square	3X	Tops
Dressbarn Button Front Shirt Blouse Womens 2X Short Sleeve Brown Accordion	2X	Tops
Chicos Poncho Blouse Womens S/M Rayon Short Sleeve Black Blue Purple	M	Tops
Weatherproof Shirt Mens 2XL XXL Cotton Button Front Short Sleeve Plaid Pocket	2XL	Tops
Lularoe NWT Irma Tunic Top Womens XL Blue Green Stretch Short Sleeve	XL	Tops
Croft & Barrow T-Shirt Womens 1X Pima Cotton Short Sleeve Brown Crew Soft	1X	Tops
Talbots T-Shirt Blouse Womens 3X Silk Lycra Stretch Short Sleeve Black Knit	3X	Tops
Lauren Ralph Lauren Navy Blue Shirt Top Tee Short Sleeve Cotton Womens L	L	Tops
George Button Front Shirt Mens 3XL XXXL Blue Plaid Short Sleeve Classic Fit 54	3XL	Tops
Woman Within T-Shirt Womens 1X 22/24 Orange Short Sleeve Cotton Square Neck Top	1X	Tops
Nike Dri Fit Polo Shirt Mens 2XL XXL Red Polyester Short Sleeve Solid	2XL	Tops
Target TakePride Shirt Womens 2XL XXL Chambray Button Front Short Sleeve Cotton	2XL	Tops
Under Armour Loose Fit HeatGear Polo Shirt Gray Short Sleeve Embroidered Mens XL	XL	Tops
Daniel Rainn Blouse Womens XL Purple Rayon Lace Short Sleeve Keyhole	XL	Tops
Croft & Barrow The Classic Tee T-Shirt Blue Cotton V-Neck Henley Short Sleeve 1X	1X	Tops
Catherines Polo Shirt Womens 3X 26/28W Suprema Cotton Spandex Black Short Sleeve	3X	Tops
Lane Seven L7 NWT T-Shirt West Coast California 2XL Cotton Short Sleeve	2XL	Tops
Philosophy Round Neck Short Sleeve Top Blouse Yellow Rayon/Stretch Womens 1X	1X	Tops
Falls Creek Polo Shirt Womens 1X Orange Cotton Spandex Polka Dot Short Sleeve	1X	Tops
Croft & Barrow The Classic Tee Womens 2XL XXL T-Shirt Short Sleeve Stretch	2XL	Tops
Catherines Blouse T-Shirt Womens 3X 26/28W Pink Short Sleeve Spandex Cotton	3X	Tops
Ralph Lauren Denim & Supply Shirt Mens Medium Cotton Short Sleeve Regular Fit	M	Tops
Jane + Delancey Button Front Shirt Womens XL Short Sleeve Rayon Animal Print	XL	Tops
Rose + Olive Blouse Womens 2X Pink Orange Paisley Rayon Spandex Short Sleeve	2X	Tops
Chaps Button Front Shirt Mens 2XL XXL Easy Care Short Sleeve Pocket Gingham	2XL	Tops
Maurices T-Shirt Blouse Womens 2X Pink Cotton Polyester Floral Short Sleeve	2X	Tops
Sara Michelle Blouse Womens 2X Polyester Green Tiered Lined Short Sleeve	2X	Tops
Cato C Est 1946 Tunic Dress Womens 22W 24W 3X Orange Paisley Short Sleeve V	3X	Tops
Terra & Sky Blouse Womens 2X 20W 22W Rayon Yellow Floral Short Sleeve Flare	2X	Tops
Croft & Barrow T-Shirt Blouse Womens 2XL XXL Short Sleeve Cotton Pink Square	2XL	Tops
Catherines Blouse T-Shirt Womens 2X 22/24W Teal Blue Purple Short Sleeve Spandex	2X	Tops
Catherines Blouse T-Shirt Womens 2X 22/24W Purple Short Sleeve Spandex Cotton	2X	Tops
Eddie Bauer Button Front Shirt Womens XL Cotton Spandex Short Sleeve Stripe	XL	Tops
Torrid T-Shirt Womens 1 1X Black Short Sleeve V-Neck Girlfriend Slub Cotton	1X	Tops
Catherines Blouse T-Shirt Womens 3X Polyester Short Sleeve V-Neck Stain	3X	Tops
Lands End Button Front Shirt Womens 16 No Iron Supima Cotton Plaid Short Sleeve	16	Tops
Perch By Blu Pepper Blouse Womens 3XL Short Sleeve Pleated Chambray Lyocell	3XL	Tops
Basic Editions Shirt Mens 3XLT Short Sleeve Woven Plaid Blue White Pocket Button	3XLT	Tops
CJ Banks T-Shirt Blouse Womens 3X Cotton Modal Short Sleeve Orange	3X	Tops
Lane Bryant Shirt Floral Knot Button Up Short Sleeve Rayon Womens 18	18	Tops
Chicago Cubs Baseball Jersey T-Shirt Mens 4XL TX3 Cool V-Neck Short Sleeve	4XL	Tops
Under Armour Polo Shirt Mens 2XL Loose HeatGear Camo Print Blue Short Sleeve	2XL	Tops
Under Armour Polo Shirt Mens 3XL Loose HeatGear Short Sleeve Orange Stained	3XL	Tops
Chicos Weekends Blouse Tee Womens 3 (US XL 16) Short Sleeve Flamingos White	XL	Tops
Truself Blouse Womens 2X Brown Cotton V-Neck Short Sleeve Eyelet Embroidered	2X	Tops
Maurices Peasant Top Shirt Blouse Knot Button Up Short Sleeve Womens 1 1X	1X	Tops
Sonoma NWT Camp Shirt Mens Medium Teal Cactus Short Sleeve Linen Cotton	M	Tops
LRL Lauren Jeans Co Henley Shirt Womens 1X Navy Blue Short Sleeve Cotton	1X	Tops
Chaps Button Front Shirt Womens XL Cotton Spandex Blue Plaid Short Sleeve Dolman	XL	Tops
Brooks Brothers Patchwork Plaid Shirt Mens XL Original Fit Cotton Short Sleeve	XL	Tops
Uniqlo Liberty London T-Shirt Womens Large Cotton Modal Floral Short Sleeve	L	Tops
Rebecca Malone T-Shirt Womens 3X Black Polka Dot Short Sleeve Cotton Blend	3X	Tops
J Jill Blouse Womens XL Luxe Supima Relaxed Tee Black/White Plaid Short Sleeve	XL	Tops
Lands End Polo Shirt Mens 3XLT Blue Pocket Short Sleeve Cotton Traditional Fit	3XLT	Tops
Alan Flusser Linen Shirt Mens Large Short Sleeve Yellow Blue Stripe Pocket	L	Tops
Lucky Brand T-Shirt Womens Medium Linen Blend White Blue Short Sleeve	M	Tops
Apt 9 Animal Print Top Shirt Blouse Short Sleeve Crew Essentials Womens 2XL XXL	2XL	Tops
Danskin Now DriMore Mesh Crew T-Shirt Short Sleeve Burgundy Polyester Womens XXL	2XL	Tops
St Johns Bay Quick Dri Shirt Womens 3X Polyester Purple Floral Short Sleeve Mesh	3X	Tops
Michael Kors Blouse Womens XL Short Sleeve Tab Polyester Spandex Blue Green	XL	Tops
Old Navy Everywear T-Shirt Womens 2XL XXL Slub Cotton Short Sleeve V-Neck White	2XL	Tops
Secret Treasures T-Shirt Dress Womens 2X/3X Colorful Short Sleeve Poly Spandex	3X	Tops
Croft & Barrow Blouse Tee Womens 2X Navy Blue Cotton Blend Short Sleeve Eyelet	2X	Tops
Ava & Viv T-Shirt Womens 2X Black Short Sleeve Cotton Modal Spandex Solid	2X	Tops
Vibe Sportswear Tunic Dress Swing Blue Womens 2X Plus Short Sleeve Tie Stretch	2X	Tops
Bobbie Brooks Button Front Shirt Womens 18W/20W 2X Short Sleeve Blue Patchwork	2X	Tops
Lucky Brand Cotton Modal Shirt Blouse Tee Short Sleeve Red Blue Womens S	S	Tops
Woman Within Button Front Shirt Womens 4X 34/36 Red Cotton Short Sleeve	4X	Tops
LOFT Ann Taylor Blouse Womens Large Cotton Spandex Short Sleeve Green Teal	L	Tops
Nike Dri Fit Polo Shirt Mens 2XL XXL Black Striped Short Sleeve Polyester	2XL	Tops
Lands End T-Shirt Womens 2X 20W 22W V-Neck Cotton Short Sleeve Blue Striped	2X	Tops
Columbia PFG Shirt Mens 2XL XXL Blue Bahama Nylon Short Sleeve Solid Fishing	2XL	Tops
Alfred Dunner NWT Blouse Womens 1X Green Short Sleeve Crinkle Polyester Lined	1X	Tops
Ellos T-Shirt Womens 2X Plus 26/28 Cotton Peace Sign Short Sleeve Pink	2X	Tops
Lularoe Carly T-Shirt Dress Womens 3XL XXXL Short Sleeve A-line Stretchy Maxi	3XL	Tops
Athleta Finish Fast Shirt Womens Small Short Sleeve Nylon Spandex Black Tee	S	Tops
Christopher Banks Blouse T-Shirt Womens XL Red Floral Short Sleeve Cotton	XL	Tops
Adidas NWT 1/4 Zip Knit T-Shirt Mens 3XL XXXL Black Polyester Short Sleeve	3XL	Tops
By Chicos T-Shirt Womens 3 (US XL) Pink White Stripe 100% Cotton Short Sleeve	XL	Tops
Rachel Zoe Linen Blouse Womens 1X White Blue Floral Short Sleeve V-neck	1X	Tops
Bobbie Brooks Blouse Womens 2X Short Sleeve Floral Pink Swing Cotton Polyester	2X	Tops
Terra & Sky Blouse Womens 3X 24W 26W Woven Plaid Cotton Short Sleeve	3X	Tops
Brooks Brothers Polo Shirt Mens XL Short Sleeve Cotton Performance Original Fit	XL	Tops
Nike Dri Fit T-Shirt Red Short Sleeve Spellout Crew Tee Mens 2XL XXL	2XL	Tops
Chaps Blouse Womens 1X Short Sleeve Striped Ruffle Cotton	1X	Tops
Simply Vera Wang Blouse Womens XL Short Sleeve Purple Tie Dye Rayon Stretch	XL	Tops
Faded Glory Blouse Womens 3X Purple Square Neck Short Sleeve Rayon Stain	3X	Tops
Faded Glory Blouse Womens 4X 26W 28W Black White Floral Cotton Short Sleeve	4X	Tops
Woman Within T-Shirt Womens 3X 30/32 Cotton Short Sleeve Red Crew Top	3X	Tops
Tahari Linen T-Shirt Womens 1X White Black Floral Short Sleeve V-Neck	1X	Tops
Terra & Sky Blouse Womens 1X Cotton Short Sleeve V-Neck Woven White Button Up	1X	Tops
Joules NWT T-Shirt Dress Womens US 14 Jersey Knit Cotton Short Sleeve Floral Red	14	Tops
Croft & Barrow The Classic Tee Womens 1X Cotton Blue Floral Short Sleeve T-shirt	1X	Tops
Studio 1940 Blouse Womens 22/24W Black Red Short Sleeve Polyester Stretch	24W	Tops
Croft & Barrow NWT T-Shirt Blouse Womens 3X Intimates Short Sleeve Cotton Poly	3X	Tops
Nike Just Do It T-Shirt Navy Blue Yellow Crew Short Sleeve Cotton Regular Mens L	L	Tops
Tru Hearts V-Neck T-Shirt Womens 3X Polyester Stretch White Short Sleeve Top	3X	Tops
Ben Hogan Performance Polo Shirt Gray Striped Short Sleeve Polyester Mens 3XL	3XL	Tops
Torrid T-Shirt Womens 3 V-Neck Pocket Tee Pink Striped Short Sleeve Cotton Blend		Tops
Massini NWT T-Shirt Womens 1X Pink V-Neck Foil Metallic Short Sleeve Stretch	1X	Tops
Knox Rose Blouse Womens 2XL XXL Black Purple Tassel Short Sleeve Pleat Cotton	2XL	Tops
Catherines Blouse Womens 1X Polyester Spandex Embellished Short Sleeve	1X	Tops
Chicos T-Shirt Womens 3 (US XL) Blue White Cotton Modal Short Sleeve Stain	XL	Tops
Catherines Suprema Collection Blouse Womens 3XWP Teal Short Sleeve Cotton V		Tops
Michael Kors T-Shirt Womens 3XL Short Sleeve Black Cotton Logo Spellout Crew	3XL	Tops
Notations Blouse Womens 3X Plus Polka Dot Orange/Pink Short Sleeve Spandex	3X	Tops
Croft & Barrow Classic Tee Womens 2X 100% Cotton Teal Green Short Sleeve T-Shirt	2X	Tops
Arizona Jeans Co NWT T-Shirt Mens Large Blue V-Neck Cotton Blend Short Sleeve L	L	Tops
Chicos Zenergy Blouse Womens 4 (US 2XL XXL 20/22) Short Sleeve USA Spandex Tee	2XL	Tops
Falls Creek Blouse Womens 1X Cotton Modal Pink Short Sleeve V-Neck Gauze Trim	1X	Tops
Chicos Blouse Womens Size 3 (US XL/16) Yellow Embroidered Short Sleeve Cotton	XL	Tops
Wrangler Wrancher Western Pearl Snap Shirt Womens 2XL XXL Short Sleeve Pink	2XL	Tops
For The Republic Blouse Womens 2X Cotton Pink Tassels V-Neck Short Sleeve	2X	Tops
LA Blues Fashion Bug NWT Blouse Womens 22/24 2X Teal Paisley Short Sleeve	2X	Tops
Nike Running T-Shirt Mens Medium Teal Dri Fit Short Sleeve Poly Stretch	M	Tops
Adidas NWT Polo Shirt Mens 2XL XXL Short Sleeve ClimaCool White Graphite Striped	2XL	Tops
Sonoma Blouse T-Shirt Womens 1X Cotton Blend Blue V-Neck Short Sleeve Dolman	1X	Tops
Croft & Barrow Henley T-Shirt Womens 3X Purple Floral Butterfly Short Sleeve	3X	Tops
Lucky Brand T-Shirt Mens 2XL XXL Orange Venice Burnout Short Sleeve V-Neck	2XL	Tops
Notations Vintage Top Shirt Blouse Brown Short Sleeve Square Neck USA Womens 2X	2X	Tops
THML Blouse Womens Medium Short Sleeve Balloon Sleeve White Floral Colorful	M	Tops
Lucky Brand Blouse Womens Medium Cotton Modal Blue Paisley Tassel Short Sleeve	M	Tops
Ava & Viv T-Shirt Womens 3X Cotton Polyester Short Sleeve V-Neck Black Tee	3X	Tops
Old Navy Button Front Shirt Womens 3X Cotton Pink Short Sleeve Pocket Collar	3X	Tops
Lauren Conrad Blouse Womens 2X Polyester Pink Floral Short Sleeve Pullover Tie	2X	Tops
Signature 1 Studio Ruffle Short Sleeve Top Shirt Blouse Cotton Crew Womens 1X	1X	Tops
Tory Burch T-Shirt Womens Medium Blue Pima Cotton Short Sleeve Crew Tee	M	Tops
C Est 1946 Cato Blouse Womens 18/20W 2X Teal Pink Short Sleeve Cotton Modal	2X	Tops
Lane Bryant Tunic Top Womens 20 Black Short Sleeve Asymmetric Polyester	20	Tops
Catherines Blouse Tunic Womens 0X Polyester Spandex Short Sleeve Light Pilling	0X	Tops
Rewind Blouse Womens 3X Mixed Media Paisley Short Sleeve Square V-Neck	3X	Tops
RVCA Button Front Shirt Mens XL Slim Fit Red Short Sleeve Pocket Cotton	XL	Tops
Wrangler Double Pocket Button Front Shirt Gray Short Sleeve Cotton Mens 3XL	3XL	Tops
Cocomo NWT Blouse Womens Medium Polyester Spandex Knot Front Short Sleeve Red	M	Tops
Maurices Blouse Womens 3X Cotton Embroidered Short Sleeve Pink Pleated	3X	Tops
Old Navy Blouse Top Womens 3X White Blue Floral Embroidered Short Sleeve Cotton	3X	Tops
J Crew NWT T-shirt Womens XL Neon Pink Burnout Made In LA USA Short Sleeve	XL	Tops
Lucky Brand Blouse Womens XS Viscose Short Sleeve V-neck Tie White Peasant	XS	Tops
Affliction AC DC T-Shirt Mens 3XL XXXL Tie Dye Embellished Short Sleeve Tee	3XL	Tops
American Giant T-Shirt Womens Large Made USA Supima Cotton Short Sleeve Crew	L	Tops
Peter Millar Summer Comfort Polo Shirt Mens XL White Blue Striped Short Sleeve	XL	Tops
BSA Boy Scouts of America Youth Shirt Medium Indiana Hoosier Blue Short Sleeve	M	Tops
Jaclyn Smith Blouse Womens 3X Polyester Spandex Short Sleeve Black Pink Striped	3X	Tops
Terra & Sky Button Front Shirt Womens 3X 24W 26W Cotton Pink Short Sleeve V-Neck	3X	Tops
BKE Buckle Tunic Top Womens XL Short Sleeve Burgundy Round Neck Stretchy Rayon	XL	Tops
Alfred Dunner Blouse Womens 3X Pink Crinkle Polyester Tiered Short Sleeve	3X	Tops
Lucky Brand Blouse Womens Large Blue Rayon Tie V Short Sleeve Floral	L	Tops
Nouveau Monde Blouse Womens 2X Crinkle Polyester Green Short Sleeve Lightweight	2X	Tops
Lucky Brand Blouse Womens 3X Plus Purple Pink Orange Viscose Short Sleeve Tie	3X	Tops
Ralph Lauren Sport T-Shirt Womens S/M Cotton Striped Pony Short Sleeve	M	Tops
East 5th Essentials Orange Crew Short Sleeve Top Shirt Blouse Stretch Womens 1X	1X	Tops
Sag Harbor Classics Textured Shirt Top Short Sleeve Orange Stretch Womens 1X	1X	Tops
Old Navy Dress Womens Large Fit Flair Waist Elastic Short Sleeve Knit T-Shirt	L	Tops
Lularoe Irma Tunic Top T-Shirt Womens 2XL XXL Purple Paisley Short Sleeve Hi Low	2XL	Tops
Torrid Blouse Womens 3 3X Rayon Button Front Tie Short Sleeve Crop	3X	Tops
Cherokee Blouse T-Shirt Womens 1X Floral Red Orange Short Sleeve Cotton V	1X	Tops
Carhartt Pocket Red Check Shirt Button Front Short Sleeve Mens Large - Reg L	L	Tops
George Denim Shirt Mens 2XL XXL 50-52 Blue Short Sleeve Cotton Button Front	2XL	Tops
Time And Tru NWT T-Shirt Maternity Womens 2XL XXL 20 Navy Blue Short Sleeve	2XL	Tops
Ralph Lauren Sport T-Shirt Womens Large Pink Pima Cotton V-Neck Short Sleeve	L	Tops
LOFT Ann Taylor Blouse Womens Large Polyester Black Floral Short Sleeve	L	Tops
Athleta Chi Dot Mesh Top Womens XL Burgundy Polyester Lycra Short Sleeve T-Shirt	XL	Tops
Woman Within T-Shirt Blouse Womens 2X 26/28 Cotton Short Sleeve White V-Neck	2X	Tops
Orvis Fishing Shirt Mens Large Blue Short Sleeve Double Pocket Polyester Dry	L	Tops
Tahari Linen Blouse Womens Medium Dark Teal Green V-Neck Short Sleeve	M	Tops
Banana Republic Eco T-shirt Bike Club Mens Large Crew Short Sleeve Burgundy L	L	Tops
Under Armour T-Shirt Womens XL HeatGear V-Neck Short Sleeve Loose Fit Pink	XL	Tops
Cynthia Rowley 100% Linen Blouse Womens Small Short Sleeve Button Accent	S	Tops
Eddie Bauer Button Front Shirt Womens XL Classic Fit Plaid Short Sleeve Pocket	XL	Tops
Massimo Dutti Short Sleeve Polo Shirt Pink Casual Fit Cotton Womens XL	XL	Tops
Knox Rose Blouse Womens Large Floral Short Sleeve Rayon V-Neck Pleat Peasant	L	Tops
Knox Rose Blouse Womens Medium Green Rayon Floral Short Sleeve Puff Crew	M	Tops
Croft & Barrow NWT T-Shirt Womens 1X Yellow/White Striped Short Sleeve Cotton	1X	Tops
J Jill Luxe Supima Pleat Back Tee Womens Large Paisley White Short Sleeve Blouse	L	Tops
Croft & Barrow Lobster Shirt Mens 3XB 3X Big Blue Short Sleeve Cotton Button Up	3XB	Tops
Croft & Barrow T-Shirt Womens 1X Cotton The Classic Tee Short Sleeve Paisley	1X	Tops
J Jill Blouse Womens Large Cotton Tassel Embroidered Pleated Short Sleeve Red	L	Tops
Polo Ralph Lauren Polo Shirt Mens Medium Classic Fit Blue Cotton M Short Sleeve	M	Tops
One World Live and Let Live Blouse Womens XL Polyester Short Sleeve V-Neck Fit	XL	Tops
Harley Davidson T-Shirt Mens Large Green Mineral Wash Short Sleeve Cotton Skull	L	Tops
Soft Surroundings T-Shirt Womens Small Blue Embroidered Short Sleeve Cotton	S	Tops
Life Is Good T-Shirt Womens XL Green Cotton Short Sleeve Crew Flower	XL	Tops
Ann Taylor Blouse Womens Large Rayon Short Sleeve Elastic Waist Yellow Blue	L	Tops

Updated lists:

List of valid size entries	replace entries	Category
7XP	7XP	Top	Tops
6XP	6XP	Blouse	Tops
5XP	5XP	Tee	Tops
4XP	4XP	Tunic	Tops
3XP	3XP	Kimono	Tops
2XP	2XP	T-Shirt	Tops
1XP	1XP	T-shirt	Tops
0XP	0XP	Shirt	Tops
3XWP	3XP	Tank	Tops
2XWP	2XP	Dress	Dresses
1XWP	1XP	Shirt Dress	Dresses
0XWP	0XP	Jumpsuit	Jumpsuits
26P	26P	Romper	Jumpsuits
24P	24P	Overalls	Jumpsuits
22P	22P	Shortalls	Jumpsuits
20P	20P	Jeans	Jeans
18P	18P	Jegging	Jeans
16P	16P	Jeggings	Jeans
14P	14P	Mom Jean	Jeans
12P	12P	Pants	Pants
10P	10P	Leggings	Pants
8P	8P	Legging	Pants
6P	6P	Pant	Pants
4P	4P	Pedal Pushers	Pants
2P	2P	Jogger	Pants
0P	0P	Joggers	Pants
00P	00P	Trouser	Pants
P7XL	P7XL	Trousers	Pants
P6XL	P6XL	Jacket	Jackets
P5XL	P5XL	Vest	Jackets
P4XL	P4XL	Coat	Jackets
P3XL	P3XL	Sport Coat	Sport Coats
P2XL	P2XL	Blazer	Sport Coats
PXL	PXL	Suit Jacket	Sport Coats
XLP	PXL	Waist Coat	Sport Coats
PL	PL	Sweater	Sweaters
LP	PL	Cardigan	Sweaters
PM	PM	Shorts	Shorts
MP	PM	Short	Shorts
SP	PS	Skirt	Skirts
PS	PS	Skort	Skirts
PXS	PXS	Polo	Polos
P2XS	P2XS	Rugby Shirt	Polos
7XB	7XB	Hoodie	Hoodies/Sweatshirts
6XB	6XB	Sweatshirt	Hoodies/Sweatshirts
5XB	5XB	Fleece Pullover	Hoodies/Sweatshirts
4XB	4XB	Hat	Misc
3XB	3XB	Wallet	Misc
2XB	2XB	Bag	Misc
1XB	1XB	Scarf	Misc
6XLT	6XLT
5XLT	5XLT
4XLT	4XLT
3XLT	3XLT
2XLT	2XLT
XLT	XLT
LT	LT
MT	MT
7XL	7XL
6XL	6XL
5XL	5XL
4XL	4XL
3XL	3XL
2XL	2XL
XL	XL
1XL	XL
L	L
M	M
S	S
XS	XS
2XS	2XS
XXS	2XS
7X	7X
6X	6X
5X	5X
4X	4X
3X	3X
2X	2X
1X	1X
0X	0X
XXXL	3XL
XXL	2XL
Large	L
Medium	M
Small	S
44W	44W
42W	42W
38W	38W
36W	36W
34W	34W
32W	32W
30W	30W
28W	28W
26W	26W
24W	24W
22W	22W
20W	20W
18W	18W
16W	16W
14W	14W
One Size	One Size
All Sizes	One Size
60	60
58	58
56	56
54	54
52	52
50	50
48	48
46	46
44	44
42	42
40	40
38	38
36	36
35	35
34	34
33	33
32	32
31	31
30	30
29	29
28	28
27	27
26	26
25	25
24	24
22	22
20	20
18	18
16	16
14	14
12	12
10	10
00	0
8	8
6	6
4	4
2	2
0	0

 

[frewert]

I just realized that Sub frewert_3 only creates Column C. It will not create Column B.

This is probably why 3XWP, Torrid sizes, and All Sizes were not working for me this morning.

 

[Akuini]

I just realized that Sub frewert_3 only creates Column C. It will not create Column B.

Sorry, I overlooked that part while updating the code.

here is some error with Short being read over Top, resulting in the wrong category.

What's the rule if there are multiple Category in a Title?
For example:

Maurices Top Womens 0X Black Floral Rayon Stretch Short Sleeve Flutter Pink

0X

Shorts

Should be Tops

It has Top & Short, so why choose Top instead of Short?

Polo Ralph Lauren Vintage Bag Blackwatch Leather Shoulder Strap Zip

Polos

Should be Misc

It has Polo & Bag, so why choose Bag instead of Polo?

 

[frewert]

• Of the 203 in the list above, all but one have "Short Sleeve" in common. I think it'd be a good rule to make that IF "Short Sleeve" exists, then ignore Short in that case.
• Similar with "Polo Ralph Lauren", "Polo Jeans Co" and "US Polo Assn", if they could be ignored, they would always accompany a category word somewhere else in the title.

 

[Akuini]

Try this:

Sub frewert_4()
Dim regEx As Object, Matches As Object
Dim i As Long, j As Long, n As Long
Dim tx As String, pt
Dim va, vb, vc, ary
Dim Flag As Boolean

t = Timer

Range("B2:C200000").ClearContents  'clear col B:C

With Range("F2", Cells(Rows.Count, "F").End(xlUp)).Resize(, 2)
    .Value = Application.Trim(.Value)  'trim trailing space in data in col A
    vb = .Value
End With



va = Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'populate va with col A data
ReDim vc(1 To UBound(va, 1), 1 To 1)

Debug.Print "Number of records  :" & " : " & UBound(va, 1)
Debug.Print "Number of Size List  :" & " : " & UBound(vb, 1)

For i = 1 To UBound(va, 1)
    tx = va(i, 1)
    
    For Each x In Split(", / \ ( ) . ; : [ ]", " ") 'replace some characters with a space (exclude ")
        tx = Replace(tx, x, " ")
    Next
    tx = " " & tx & " "   'add space at the beginning and end of each record
    tx = Replace(tx, """", "ZZZ")  'replace " with ZZZ so word/number with " will be ignored
    
    tx = Replace(tx, "Short Sleeve", " Sleeve")             'replace "Short Sleeve" with " Sleeve"
    tx = Replace(tx, "Polo Ralph Lauren", " Ralph Lauren")
    tx = Replace(tx, "Polo Jeans Co", " Jeans Co")
    tx = Replace(tx, "US Polo Assn", "US Assn")
    tx = Replace(tx, "US Polo Assn", "US Assn")
    va(i, 1) = Replace(tx, " Inseam", "Inseam")             '   Maybe ignore "(number) Inseam"
    
Next

    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
    ary = Split("Ties|Scarves|Wallets|Hats|Bags", "|")
            
'            ============= Size =============================================
            For i = 1 To UBound(va, 1)
                Flag = False
                tx = va(i, 1)
                
                For Each x In ary  'deal with "Ties|Scarves|Wallets|Hats|Bags"
                     If InStr(tx, " " & x & " ") Then Flag = True: Exit For
                Next
                
                If Flag = False Then   '
                    If InStr(tx, " " & "Torrid" & " ") Then           'deal with Torrid
                        For Each x In Split("0 1 2 3 4 5 6", " ")
                            If InStr(tx, " " & x & " ") Then
                                vc(i, 1) = x & "X"
                                Flag = True: Exit For
                            End If
                        Next
                    End If
                End If
                
                If Flag = False Then
                    For j = 1 To UBound(vb, 1)
                        If InStr(tx, " " & vb(j, 1) & " ") Then  'check if a record has items in list in col F
                            vc(i, 1) = vb(j, 2)                  'if True then add col G value to variable vc
                            Flag = True: Exit For
                        End If
                    Next
                End If
                
                If Flag = False Then 'if the above cheking give empty result
                    For Each pt In Split(" [0-9]{2}X[0-9]{2} | [0-9]{1,2}[MRLST] ", "|")  'use this regex pattern to check
                        .Pattern = pt
                        If .test(tx) Then
                            Set Matches = .Execute(tx)
'                             Debug.Print i & " : " & Matches(0) & " : " & Val(Matches(0))
                            vc(i, 1) = Val(Matches(0))    'if there's a match then add the match to variable vc
                            Flag = True: Exit For
                        End If
                    Next
                End If
           

            Next
            
'put the result (of Category) in col C:
Range("B2").Resize(UBound(vc, 1), 1) = vc
            
            

            
'===================== Category ======================

    With Range("H2", Cells(Rows.Count, "H").End(xlUp)).Resize(, 3)
        .Value = Application.Trim(.Value)                          'trim trailing space in data in col H:I
        vb = .Value                                                'load data to vb from col H:I
    End With
    
    For i = 1 To UBound(vb, 1)
        vb(i, 3) = Len(vb(i, 1))  'load col H word length to 3rd col in vb
    Next
    
    vb = WorksheetFunction.Sort(vb, 3, -1)  'sort vb by word length

    
    ReDim vc(1 To UBound(va, 1), 1 To 1)
        For i = 1 To UBound(va, 1)
            Flag = False
            tx = va(i, 1)
            For j = 1 To UBound(vb, 1)
                If InStr(tx, " " & vb(j, 1) & " ") Then  'check if a record has items in list in col H
                        vc(i, 1) = vb(j, 2)                  'if True then add col I value to variable vc
                        Flag = True: Exit For
                End If
            Next
        Next
            
End With
'put the result (of Category) in col C:
Range("C2").Resize(UBound(vc, 1), 1) = vc
Debug.Print "Number of Category List  :" & " : " & UBound(vb, 1)
Debug.Print "Completion time:  " & Format(Timer - t, "0.00") & " seconds"

End Sub

 

[frewert]

We are error free now on both Size and Category! Nice job on this.