Finding substring in google spreadsheet

Here is how to find substrings in google spreadsheet.

Say you have a long string in cell AJ13 and want to see if the substring Obama in China is in there. This is how you do it.

=IF( ISNUMBER( FIND( "Obama in China" ; AJ13 ) ) ; "YES" ; "NOT SURE" )

You can nest it for more interesting result. (But not too long…)

=IF( ISNUMBER( FIND( "Obama in China" ; AJ13 ) ) ; "China" ; IF( ISNUMBER( FIND( "Obama in India" ; AJ13 ) ) ; "India" ; IF( ISNUMBER( FIND( "Obama in Europe" ; AJ13 ) ) ; "Europe" ; IF( ISNUMBER( FIND( "Obama in Africa" ; AJ13 ) ) ; "Africa" ; "NOT SURE" ) ) ) )

That is 4-level deep, basically to start a new if-statement at the else section.
=IF( ISNUMBER( FIND( “Obama in China” ; AJ13 ) ) ; “China” ; IF( ISNUMBER( FIND( “Obama in India” ; AJ13 ) ) ; “India” ; IF( ISNUMBER( FIND( “Obama in Europe” ; AJ13 ) ) ; “Europe” ; IF( ISNUMBER( FIND( “Obama in Africa” ; AJ13 ) ) ; “Africa” ; “NOT SURE” ) ) ) )

ref: https://productforums.google.com/forum/#!topic/docs/YhIKnBsoXsc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s