comparing 2 strings of Texts

[removefromp]

Hi all, totally new here, treat me as a retard when I say something stupid, and thanks for your help in advance.

I have two columns of texts. 1 column contains category, such as apple, orange, banana, etc. Another column contains raw data but part of it has the category name, such as apple11111, orange is good, banana1212 soso, etc.

I have 1000 thousand raw data, 25 categories.

I would like to categorize all the raw data into the 25 categories. In another words, for all the raw data that contains the text "apple", it would be categorizes as "apple".

I don't necessarily need VBA to do the above if a simple excel function could do the same.

Thanks a lot.

[kadghar]

Hi all, totally new here, treat me as a retard when I say something stupid, and thanks for your help in advance.

I have two columns of texts. 1 column contains category, such as apple, orange, banana, etc. Another column contains raw data but part of it has the category name, such as apple11111, orange is good, banana1212 soso, etc.

I have 1000 thousand raw data, 25 categories.

I would like to categorize all the raw data into the 25 categories. In another words, for all the raw data that contains the text "apple", it would be categorizes as "apple".

I don't necessarily need VBA to do the above if a simple excel function could do the same.

Thanks a lot.

hmm, i'd say INSTR will help you

lets say you've read the raw data into a string array, and your 3 categories are apple, orange and banana

then something like this will do (assume Arr1 has the raw data)

dim i as long, j as integer
dim CatArr(1 to 3) as string
catarr(1) = "apple"
catarr(2) = "orange"
catarr(3) = "banana"
for j = 1 to 3
for i = lbound(arr1) to ubound(arr1)
    if instr(Arr1(i), catarr(j)) <> 0 then
        'your code to store the category
    end if
next

Also consider using UCASE or LCASE to avoid problems with upper/lower case.

For huge amounts of data in excel check this thread (specially my very smart and useful answer ^.^)



HTH