Cara menggunakan google sheets string compare

Question

If you’ve ever been in a situation where you’re working with (yet another) spreadsheet in Excel or Google Docs and can’t figure out how to determine if a particular cell contains a keyword or phrase you’re searching for, then you’ve come the right place.

Table of Contents Show

The Trusty SEARCH Function
A Simple Example
Adding the ISNUMBER Function
Bagaimana cara wrap text di spreadsheet?
Sebutkan langkah langkah membuat Google spread sheet baru?
Bagaimana mencari rata
Bagaimana cara menggunakan Google Sheet?

Surprisingly, neither application provides a built-in SUBSTRING search function, but we can easily replicate that same functionality without too much trouble.

The Trusty SEARCH Function

At the core, we can determine if a cell contains some particular text by making use of the SEARCH function. The arguments are the same for both Excel and Google Docs and look like this:

SEARCH(find_text, within_text, [start_at])

find_text is the text you are searching for.
within_text is the text or cell you are searching in.
start_at is an optional argument that allows you to specify the character number of the within_text at which point to begin the search. By default this is assumed to be 0, meaning it will search the entire string.

A Simple Example

We have a basic spreadsheet created for this example that lists books and their associated authors:

TitleAuthorThe HobbitJ.R.R. TolkienThe SilmarillionJ.R.R. TolkienMoby-DickHerman Melville

Now let’s add another cell off to the side for the text we want to

=SEARCH($F$1, A2)

0, along with two new columns where we’ll perform our SEARCH function above to see if that value (the

=SEARCH($F$1, A2)

2 or

=SEARCH($F$1, A2)

3) contains the

=SEARCH($F$1, A2)

0 text.

TitleAuthorTitle MatchAuthor MatchSearch for:TolkienThe HobbitJ.R.R. TolkienERROR8 The SilmarillionJ.R.R. TolkienERROR8 Moby-DickHerman MelvilleERRORERROR

The actual formula within the

=SEARCH($F$1, A2)

5 column looks like the following, using our SEARCH function to search the

=SEARCH($F$1, A2)

7 column cell of that row for the text within the

=SEARCH($F$1, A2)

8 box, which in this case is just the last name

=SEARCH($F$1, A2)

9:

=SEARCH($F$1, A2)

The problem we see here is the SEARCH function returns an error if no match is found. While the word

=SEARCH($F$1, A2)

9 was found as part of the first and second

=SEARCH($F$1, A2)

3 cells (starting at character number 8), it failed to find any matches in the other cells and thus produced that ugly error, which isn’t any good for us.

Adding the ISNUMBER Function

The simplest solution here is to wrap the above

=ISNUMBER(SEARCH($F$1, A2))

3 function in yet another function which will convert the output from the SEARCH function into a boolean (true or false) value. For this purpose, we’ll use the

=ISNUMBER(SEARCH($F$1, A2))

5 function. As the name suggests, this function simply checks if the provided value is, in fact, a number or not, and returns a value of

=ISNUMBER(SEARCH($F$1, A2))

6 or

=ISNUMBER(SEARCH($F$1, A2))

7 accordingly. Thus

=ISNUMBER(SEARCH($F$1, A2))

8 will return

=ISNUMBER(SEARCH($F$1, A2))

6, while SUBSTRING0 will return

=ISNUMBER(SEARCH($F$1, A2))

7.

Now our above SEARCH function is wrapped in

=ISNUMBER(SEARCH($F$1, A2))

5 and looks like this:

=ISNUMBER(SEARCH($F$1, A2))

Updating all the cells in our table accordingly gets rid of the ugly errors and provides a simple

=ISNUMBER(SEARCH($F$1, A2))

6 or

=ISNUMBER(SEARCH($F$1, A2))

7 result in the SUBSTRING6 columns:

TitleAuthorTitle MatchAuthor MatchSearch for:TolkienThe HobbitJ.R.R. TolkienFALSETRUE The SilmarillionJ.R.R. TolkienFALSETRUE Moby-DickHerman MelvilleFALSEFALSE

There we have it! A simple yet effective combination of functions to allow you to easily determine if a particular cell contains a substring of text and returns a useful boolean value as a result.

The Google Sheets connector has been deprecated. To use a Google Sheet data source, you'll need to access it through the Google Drive connector instead.

Tip: To connect to a Google Sheet that isn't in your drive, you can paste the URL into the search box.

Connect to Google Drive. For more information, see Google Drive.
Find your Google Sheet by browsing, searching by name, or pasting the URL into the search box.
Select the Google Sheet.

Note that the Google Sheet can't contain a pivot table on any tab or the connection will fail.

Bagaimana cara wrap text di spreadsheet?

Klik menu “Format”, maka akan muncul ragam pilihan fitur lain yang bisa kamu pilih sesuai kebutuhan. Arahkan panah mouse atau jari kamu ke bagian bawah. Temukan fitur “Wrap Text”. Jika Spreadsheet kamu menggunakan Bahasa Indonesia, maka nama fiturnya akan berubah menjadi “Pengemasan Teks”.