Forum Discussion
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 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
4 Replies
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.
= 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.
