Finding a character from a list in a sheets cell

• Introduction
• Method

How to use the FIND function with an array of values to search for individual characters or words.

Introduction

In Google Sheets you use the arrayformula function in conjunction with find to find the first instance of any value from an array in a single cell.

Method

Imagine you wanted to remove the numbers at the end of a cell value, but you didn’t know in advance which numbers they would be, or how long they would be?

You can use the following formula to get the position of the first instance of a number in a cell value (Cell A1 in this example).

INDEX(SORT(ArrayFormula(FIND({0;1;2;3;4;5;6;7;8;9},A1))),1,1)

If this formula was applied to the string Fred123 it will return 5 as ‘1’ is the 5th character in the string. It works by sorting the response array from the find function and returning the first element (using index).

To return only the part of the string before the number, we can combine it with the mid function, like this:

IFERROR(MID(A1,1,INDEX(SORT(ArrayFormula(FIND({0;1;2;3;4;5;6;7;8;9},A1))),1,1)-1),"")

Or, to return just the number, simply change around the parameter order a litte:

=IFERROR(MID(A1,INDEX(SORT(ArrayFormula(FIND({0;1;2;3;4;5;6;7;8;9},A1))),1,1), 1000),"")

The iferror function is used to catch the scenario where there are no numbers in the string, and just return nothing (although you could also adapt this to return the whole string if preferred).

You can supply any list you want, including words, so you could also adapt the formula to return a true or false value if any character / word in a list occurs in a cell, like:

IF(IFERROR(INDEX(SORT(ArrayFormula(FIND({0;1;2;3;4;5;6;7;8;9},A1))),1,1), -1)>=0,true,false)

IF(IFERROR(INDEX(SORT(ArrayFormula(FIND({"and";"the";"or";"to"},A1))),1,1), -1)>=0,true,false)

Remember that find is case-sensitive, so you may want to adjust your ‘search’ list to accomodate this.