Forum Discussion

ChrisC365's avatar
ChrisC365
Brass Contributor
Feb 04, 2020
Solved

How to identify letter combinations in a cell value

Hi Needing some help with identifying what type of grant I have on worksheet using a formula.  Each grant  has its own project code, the format or letters dictate what category of award it its.  For...
excel
  • Charla74's avatar
    Charla74
    Feb 04, 2020
    ChrisC365

    This formula would work but assumes that what you have displayed is the complete list:

    =IF(LEFT(B3,2)="RS","INTERNAL GRANT",IF(LEFT(B3,1)="D","GENERAL GRANT","KEY GRANT"))
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 13, 2020

ChrisC365 

While I think of it ...

It is possible to search for slightly more complex substrings using COUNTIFS and wild card searches

= IFS(
  COUNTIFS([@Code],"RS*"),"INTERNAL GRANT",
  COUNTIFS([@Code],"D*"),"GEN GRANT",
  TRUE, "KEY GRANT" )

Since the formula searches only a single cell, the count will be 0 or 1.

 

An advantage of the table is that the formula range extends as data is appended to the table.

One less manual operation is one less opportunity for error.

Resources