If cell contains A, write X - or - if cell contains B, write Y

[Evan_Dobos]

Hey guys,

I have hundreds of records that contain one of 12 combinations of initials. I want to create a new column that finds those initials and depending on what they are, write the entire name.

For example:
Column A includes:

2017-02-07.boe_.ag_.pdf
2017-02-07.bos_.ag_.pdf
2017-02-09.bof_.ag_.pdf

I want the formula for Column B to find the initials, and write in the full name following these rules:

• if it finds “boe”, write “Board of Education”
• if it finds “bos”, write “Board of Selectmen”
• if it finds “bof”, write “Board of Finance”

So, the end result in Column B would be:

Board of Education
Board of Selectmen
Board of Finance

Thanks for any and all help with this!

If cell contains A, write X - or - if cell contains B, write Y

[Chris_Connacher]

Hi Evan,

This appears to be a fairly simple solution, however, it could get more complex if there are more variables than the ones you’ve listed. Try the formula below in Column B and you’ll be able to return the full name based on the 3 digit initials.

IF(MID({Column A},12,3)=“boe”,“Board of Education”,IF(MID({Column A},12,3)=“bos”,“Board of Selectmen”,IF(MID({Column A},12,3)=“bof”,“Board of Finance”,"")))

Let me know if you have any questions!

[Justin_Barrett · Accepted solution]

This could be simplified using the SWITCH() function, which is designed for just this scenario. It looks at a single thing, and switches the output depending on what it finds.

SWITCH(
    MID({Column A}, 12, 3),
    "boe", "Board of Education",
    "bos", "Board of Selectmen",
    "bof", "Board of Finance"
)

The first piece is the thing being examined. Following that are pairs of items, with the first being the item to find, and the second being the item to return if found.

[Evan_Dobos]

Cool! Thanks for the help Chris & Justin.
I do have some scenarios where the initials aren’t consistently located in the record. For example:

2017-02-07.boe_.ag_.pdf
17.2.7_boe.pdf
boe_2-7-17

Is there a way to locate the initials regardless of where they are?

[Justin_Barrett]

For that, you’ll need to revert back to the nested IF collection, using SEARCH() to look for those initials. The only problem you might encounter is if some other part of the filename matches those initials, though from the samples you’ve provided that seems unlikely.

IF(
    SEARCH("boe", {Column A}),
    "Board of Education",
    IF(
        SEARCH("bos", {Column A}),
        "Board of Selectmen",
        IF(
            SEARCH("bof", {Column A}),
            "Board of Finance"
        )
    )
)

[Maria]

Hi JustinThanks for this have been looking for ages to do this but has taken me ages to work out what I was actually searching for!

So I have copied your formula and adapted but would love to know if I can take it further.

For example if I wanted “boe” “bos” and “bof” to return Board and then add other options say “one” “two” “three” to return Numbers. So I can have various text options giving selected returns without having to repeat the return each time. Hope I haven’t confused you can send a screenshot if it helps?

Thank you, Maria

[Justin_Barrett]

I’ve got an idea of how this might work for your situation, but I’d prefer to see a screenshot to ensure that I’m going down the right path.

[Maria]

Wow! Thanks for such a speedy reply.

My objective is: to pull regular keywords from a bank statement where the rest of text can vary to be able to automatically categories transactions using the formula field.

Hope this makes sense?

[Justin_Barrett]

Yes, that makes sense, and assembling the formula should be fairly easy. In short, I suggest using a series of concatenated IF() functions, each one isolated on finding keywords for a specific category. Here’s where to start using the examples you provided.

IF(
    OR(
        FIND("MATYSIAK", {Column A}),
        FIND("HOLMES", {Column A})
    ),
    "RENT"
)
&
IF(
    OR(
        FIND("AUBURN SEC", {Column A}),
        FIND("MORTGAGE EXPRESS", {Column A})
    ),
    "MORTGAGE"
)

Provided that your keywords are specific and unique, only one IF() function will return a non-empty string, with the rest returning empty strings. Concatenating one or more empty strings onto a non-empty string won’t add any extra characters, so the final result will be a single clean string.

FWIW, I used FIND() instead of SEARCH() like the original had, but it doesn’t matter which one you use. You could replace each “FIND” with “SEARCH” and still get the same end result.

[Maria]

Thanks so much I am now seeing so many more uses for this in my system - so long I knew there was a way but not how!

So I now have my categories field automated would like to split into debit/credit options. To take to next step - I would love your help please?

I have another field with formulas to determine whether a transaction is debit or credit. I would now like to include that result in this formula so I have two fields of this one showing credit/categories and one with debit/categories. I’m not quite sure how to add it in?