Extracting strings of text from cells

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

If your data is in a consistent format, then how about text to columns. If it is varied then how about attaching an example file, showing your raw data, and how / why it should be split out.

rylo

Basically, I am looking to take the texts in column A and look for exact matches from sheet 2. Any help at how to get started would be much appreciated. Thanks. By the way, there are thousands of these "raw" texts I will need to go through--thus the need for a program like a formula or VB code.

Hi

This will get most of what you are chasing. You could build your arrays from reference data, but I've just hard coded things to give an example.

Please Login or Register  to view this content.

HTH

rylo

This looks great. How would I incorporate a referenced array of data--say A2:B50--in place of the hard written potions?

Hi

As your array only needs to have one side of things something like

Please Login or Register  to view this content.

rylo

This is working very well for the most part. I am now having issues when trying to incorporate the ranges from sheet2. The error I get is on the Instr function for the shoelaces:

Sub air()
shoearr = Range(Range("Sheet2!C2"), Range("Sheet2!C2").End(xlDown))
lacearr = Array("White Shoe Laces", "Black Shoe Laces", "Yellow Shoe Laces", "Green Shoe Laces", "Brown Shoe Laces", "Blue Shoe Laces", "Red Shoe Laces")
specarr = Array("Air Jordan", "Kobe Bryant", "Pumps", "Shaqs", "Nike Air", "Long", "Full tongue")
For Each ce In Range("A2:A50")
arr = Split(ce.Value, " ")
'assumes year is only numeric in string
For i = LBound(arr) To UBound(arr)
If IsNumeric(arr(i)) Then[/COLOR] ce.Offset(0, 1).Value = arr(i)
Next i
'shoe type
For i = LBound(shoearr) To UBound(shoearr)
If InStr(1, ce, shoearr(i)) > 0 Then ce.Offset(0, 2).Value = shoearr(i)
Next i
'lace type
For i = LBound(lacearr) To UBound(lacearr)
If InStr(1, LCase(ce), LCase(lacearr(i))) > 0 Then ce.Offset(0, 3).Value = lacearr(i)
Next i
'special type
For i = LBound(specarr) To UBound(specarr)
If InStr(1, LCase(ce), LCase(specarr(i))) > 0 Then ce.Offset(0, 4).Value = specarr(i)
Next i
Next ce
End Sub

The error states the subscript is out of range. What am I missing? Thanks ahead.

Hi

Notice the revised structure of the array call when using the range.

Please Login or Register  to view this content.

rylo