populate cell based on part of text in another cell


Can anyone help me with auto populating cells based on what the text contains in another cell?
for example cells in my coloumn B have long text, I  want coloumn M cells to only conatin the word "United States" or "Canada" .
So e.g. if B4 contains the word "Canada" I want M4 to be autopopulated with the word "Canada" and if B4 contains the word "America" I want M4 to be autopopulated with the word "United States"
I'm hoping it is possible to do this without a VBA.




Selected Answer

If your data is in Column A and you wish results in Column B, then use the following formula in Cell B4 

=IF(ISNUMBER(SEARCH("*States*",A4)),"United States",IF(ISNUMBER(SEARCH("*Canada*",A4)),"Canada","NA"))

I'm not sure how this will display, so I'm copying info here too ~ =IF(ISNUMBER(SEARCH("*States*",A4)),"United States",IF(ISNUMBER(SEARCH("*Canada*",A4)),"Canada","NA")).

You can also put your info into a table in excel, then Filter on "Contains" and type in Canada, once you have filtered data, you can then copy CANADA down all of the filtered cells, then filter on 'contains' United States and copy down the filtered range.



this one didn't work for me but definitely helped.. I ended up using this (3rd option was USVI) :
=IF(ISNUMBER(SEARCH("*USVI*";E2171));"USVI"; IF(ISNUMBER(SEARCH("*Canada*";E2171));"Canada"; IF(ISNUMBER(SEARCH("*States*";E2171));"United States"; IF(ISNUMBER(SEARCH("*America*";E2171));"United States";""""))))
LRita (rep: 2) Sep 8, '17 at 2:32 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login