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

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

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!

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.

2 Likes

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?

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"
        )
    )
)