Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to review and sort my expenditures in a spreadsheet where I have all my transactions listed. Is there a way to automatically label or tag to categorize the transactions like in a budget program. For instance if the word "taco" is found in column B, then Restaurant shows up in column D. Or if "Lowes" in Column B, then Repair supplies shows up in column D.

share|improve this question

1 Answer 1

This is perhaps not the most elegant way to do it (and not exactly what you asked for)... but it is fairly quick. and will also allow you to have multiple tags for an item (should you need that)

Basically you can add the tags across in the additional columns to the right. Then use the following formula in C2 (where B contains transaction text.)

=IFERROR(FIND(C$1,UPPER($B2)),0)>0

a quick explanation of the functions

FIND looks for the text of 1 cell (C1 in this case) inside of another cell (B2 in this case) and returns a number where the string starts. However this function will return an error if the text is not found so I am using

IFERROR will check to see if the first argument (in this case our FIND function) returns an error, if no error it will return the contents of the find function. If there is an error it will return the second argument (in this case 0)

UPPER Converts the text in a cell to upper case. FIND is case sensitive so "Beer" is different than "BEER" is different from "BeEr" since we probably want to group them UPPER will force them all to BEER.

These 3 functions combine to now return a number if the text is found and 0 if no tag is found. lastly I add >0 to the end to make excel return TRUE/FALSE instead of a number.

If you want to look for a different word to link to the category you can replace C1 with text. If you want multiple criteria for a single tag you can chain them together using the OR

ie

=OR(IFERROR(FIND("CRITERIA ONE",UPPER($B2)),0)>0,IFERROR(FIND("CRITERIA TWO",UPPER($B2)),0)>0)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.