By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

comparing 2 strings of Texts

P: 5
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.
Jun 5 '08 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
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)

Expand|Select|Wrap|Line Numbers
  1. dim i as long, j as integer
  2. dim CatArr(1 to 3) as string
  3. catarr(1) = "apple"
  4. catarr(2) = "orange"
  5. catarr(3) = "banana"
  6. for j = 1 to 3
  7. for i = lbound(arr1) to ubound(arr1)
  8.     if instr(Arr1(i), catarr(j)) <> 0 then
  9.         'your code to store the category
  10.     end if
  11. 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
Jun 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.