How to identify letter combinations in a cell value

[ChrisC365]

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 example, the followung table - 

Column in Yellow is my source column, the column in Green is what I want to show each award represents or Category.  The text in RED is to showing you the key letters that help identify that type of award.  I have tried FIND, SEARCH and LEFT or combination of and I am stuck.  Any suggestions, or links to Excel functions, welcome.  I want to use a formula rather than any VBA as the sheet is used my several people and adding a macro function will not work in my situation.

Chris

[#2 · Accepted solution]

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

[#3]

@Charla74 

Many thanks, I can now where I have been going wrong, I have been adding the OR function to my IF statement. Your solution works great thanks.

Chris

[#4]

@ChrisC365 

= IFS(
  LEFT([@CODE],1)="D", "GEN GRANT",
  LEFT([@CODE],2)="RS", "INTERNAL GRANT",
  TRUE, "KEY GRANT" )

Same idea but uses Table referencing and the IFS function.

[#5]

@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.