You want the FIND()
and/or SEARCH()
function.
Usage:
FIND(find_text, within_text)
returns the starting position of the first text string
within the second text string (starting at position 1)
So FIND("lunch", "lunch with customer")
returns 1,
and FIND("lunch", "business lunch")
returns 10.
If the first string is not found in the second, this returns a #VALUE!
error value.
SEARCH()
is like FIND()
except for the fact that FIND()
is case-sensitive
and SEARCH()
is not. So
FIND("lunch", "Lunch with customer")
returns #VALUE!
but
SEARCH("lunch", "Lunch with customer")
returns 1
I’ll assume that you’ll want to use SEARCH()
, the case-insensitive one.
You’ll want to set up an array like this:
It’s probably better to do this in a separate sheet; let’s call it Key-Sheet
.
Then, on your data sheet: If your free-form description is in column A
(starting in cell A1
), enter the following into cell B1
:
=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))
and press Ctrl+Shift+Enter, to make it an “array formula”.
(It will display in the formula bar in braces.)
Explanation:
SEARCH('Key-Sheet'!$A$1:$A$7,$A1)
– for each keyword from column A
of the key sheet (“coffee”, “lunch”, “dinner”, etc…), search for it in the description in the current row, column A
, of the data sheet (e.g., “business lunch”).
This will create an array containing { #VALUE!
; 10
; #VALUE!
; … }
(seven elements (in this example), one per keyword;
the second one shows the result for “lunch”, which is in 'Key-Sheet'!A2
).
IFERROR(…,LEN($A1)+1)
– replace #VALUE!
values with 15
, which, being LEN("business lunch")+1
, cannot possibly be a valid return value from SEARCH()
(and which, in fact, is higher than any possible valid return value from SEARCH()
), but which is a valid number.
So now our array is { 15
; 10
; 15
; … }.
MIN(…)
– extract the minimum value from the array: in this example, 10
.
In general, this will be the (first) successful return from SEARCH()
.
=MATCH(…, …)
– note that the second parameter to MATCH()
is the same as the first bullet, above.
So we are looking for 10
in the array { #VALUE!
; 10
; #VALUE!
; … }.
This returns the position of the 10
, which is 2,
corresponding to the fact that A1
on the data sheet (“business lunch”) contains “lunch”,
which is in the 2nd row of the Key-Sheet.
To get the expense category,
it’s a simple matter of indexing into column B
of the Key-Sheet.
Set cell C1
to =OFFSET('Key-Sheet'!$B$1,B1-1,0)
.
(This does not need to be an array formula.)
Note (as foreshadowed above) that, if an expense description contains multiple keywords, this will find only the first one.
If you don’t want to bother with the intermediate value, you can just compute
=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)
This does need to be an array formula.
P.S. the FIND()
and SEARCH()
functions have an optional third argument:
SEARCH(find_text, within_text, [start_num])
So
SEARCH("cigar", "Sometimes a cigar is just a cigar.")
returns 13
but
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)
returns 29
I don’t see any reason for you to use it.