200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
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.
Thanks!
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.