Search within a string for multiple values and output in a different cell

[rogerbij]

Dear all,

I have a database of 1500 records and I need to put descriptions from one cell into categories in another. I dont want to do this manually!


The cells with the header 'description' contain contents like:

-Problem with inputs

-would like access to credit

-Need help with marketing

-Tractor would be a big help


In the cell beside this, I want to put those descriptions into categories. So I have a new header called 'categories'. I would like the output to be:

-Inputs

-Credit

-Marketing

-Mechanisation


Now, I worked out that I can do this for a single item using =IF(SEARCH("credit", [@Description]), "credit") but it won't work if I use nested IF statements to search for and return multiple values.


Any suggestions on how to do this are greatly appreciated!

Roger

 

[Faseeh]

Hi rogerbij,


Welcome to the Forums!!!!


With your data in A1:A4, try this enter in B1:


=IFERROR(CHOOSE(VALUE(SUM(ROUNDUP(IFERROR(1/SEARCH({"Inputs","Credit","Marketing","Mechanisation"},A1,1),0),0)*{1,2,3,4})),"Inputs","Credit","Marketing","Mechanisation"),"")


Ctrl+Shift+Enter & Drag down...


Regards,

 

[rogerbij]

Ahah! Thanks Faseeh, that looks like one of those famous arrays that I have been hearing about?


On my own I got this working, but then hit the maximum number of If statements allowed in 2010. I suppose with your method there is no maximum?


=IF(ISERROR(SEARCH("water", [@[Description 1 most important thing]])),IF(ISERROR(SEARCH("price", [@[Description 1 most important thing]])),IF(ISERROR(SEARCH("irrigation", [@[Description 1 most important thing]])),IF(ISERROR(SEARCH("powertiller", [@[Description 1 most important thing]])),IF(ISERROR(SEARCH("market",[@[Description 1 most important thing]])),IF(ISERROR(SEARCH("fertilizer",[@[Description 1 most important thing]])), IF(ISERROR(SEARCH("herbi",[@[Description 1 most important thing]])), IF(ISERROR(SEARCH("extension",[@[Description 1 most important thing]])), IF(ISERROR(SEARCH("train",[@[Description 1 most important thing]])),IF(ISERROR(SEARCH("seed",[@[Description 1 most important thing]])),"XXXXXXXXXX", cat_seed), cat_training),cat_training), cat_inputs),cat_inputs), cat_marketing),cat_mechanisation), cat_irrigation),cat_marketing), cat_irrigation)

 

[Faseeh]

Thanks, With CHOOSE() you can go up-to 254 matches, beyond that it will create problem. How many matching criteria do you have?

 

[rogerbij]

Oh about 20 or so... nearly done with the random exceptions!

 

[3G]

Can another option be to add the classification column. Then, simply use the text filter on the column with those key words. finally add the classification in the column, and simply copy down on the filtered items.


Just another way to possibly skin a cat.

 

[NARAYANK991]

Hi Roger ,


An alternative :


=INDEX(Keywords,MATCH(TRUE,IFERROR(SEARCH(Keywords,A2),0)<>0,0))


entered as an array formula , using CTRL SHIFT ENTER. You can enter this in B2 , and copy it downwards.


Keywords is a named range which has all your keywords e.g. it can be $C$2:$C$5 containing the following :

[pre]

Inputs
Credit
Marketing
Mechanisation

[/pre]
Narayan