SOLVED

How to identify letter combinations in a cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-1150942%22%20slang%3D%22en-US%22%3EHow%20to%20identify%20letter%20combinations%20in%20a%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1150942%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3ENeeding%20some%20help%20with%20identifying%20what%20type%20of%20grant%20I%20have%20on%20worksheet%20using%20a%20formula.%26nbsp%3B%20Each%20grant%26nbsp%3B%20has%20its%20own%20project%20code%2C%20the%20format%20or%20letters%20dictate%20what%20category%20of%20award%20it%20its.%26nbsp%3B%20For%20example%2C%20the%20followung%20table%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20268px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168997iA8DCA3BCC26D8AE5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22example.PNG%22%20title%3D%22example.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20in%20Yellow%20is%20my%20source%20column%2C%20the%20column%20in%20Green%20is%20what%20I%20want%20to%20show%20each%20award%20represents%20or%20Category.%26nbsp%3B%20The%20text%20in%20RED%20is%20to%20showing%20you%20the%20key%20letters%20that%20help%20identify%20that%20type%20of%20award.%26nbsp%3B%20I%20have%20tried%20FIND%2C%20SEARCH%20and%20LEFT%20or%20combination%20of%20and%20I%20am%20stuck.%26nbsp%3B%20Any%20suggestions%2C%20or%20links%20to%20Excel%20functions%2C%20welcome.%26nbsp%3B%20I%20want%20to%20use%20a%20formula%20rather%20than%20any%20VBA%20as%20the%20sheet%20is%20used%20my%20several%20people%20and%20adding%20a%20macro%20function%20will%20not%20work%20in%20my%20situation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1150942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1150991%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20identify%20letter%20combinations%20in%20a%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1150991%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544914%22%20target%3D%22_blank%22%3E%40ChrisC365%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20formula%20would%20work%20but%20assumes%20that%20what%20you%20have%20displayed%20is%20the%20complete%20list%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(LEFT(B3%2C2)%3D%22RS%22%2C%22INTERNAL%20GRANT%22%2CIF(LEFT(B3%2C1)%3D%22D%22%2C%22GENERAL%20GRANT%22%2C%22KEY%20GRANT%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1151247%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20identify%20letter%20combinations%20in%20a%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1151247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%20I%20can%20now%20where%20I%20have%20been%20going%20wrong%2C%20I%20have%20been%20adding%20the%20OR%20function%20to%20my%20IF%20statement.%20Your%20solution%20works%20great%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1577741%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20identify%20letter%20combinations%20in%20a%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1577741%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544914%22%20target%3D%22_blank%22%3E%40ChrisC365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20IFS(%0A%20%20LEFT(%5B%40CODE%5D%2C1)%3D%22D%22%2C%20%22GEN%20GRANT%22%2C%0A%20%20LEFT(%5B%40CODE%5D%2C2)%3D%22RS%22%2C%20%22INTERNAL%20GRANT%22%2C%0A%20%20TRUE%2C%20%22KEY%20GRANT%22%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESame%20idea%20but%20uses%20Table%20referencing%20and%20the%20IFS%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586222%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20identify%20letter%20combinations%20in%20a%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544914%22%20target%3D%22_blank%22%3E%40ChrisC365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20I%20think%20of%20it%20...%3C%2FP%3E%3CP%3EIt%20is%20possible%20to%20search%20for%20slightly%20more%20complex%20substrings%20using%20COUNTIFS%20and%20wild%20card%20searches%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20IFS(%0A%20%20COUNTIFS(%5B%40Code%5D%2C%22RS*%22)%2C%22INTERNAL%20GRANT%22%2C%0A%20%20COUNTIFS(%5B%40Code%5D%2C%22D*%22)%2C%22GEN%20GRANT%22%2C%0A%20%20TRUE%2C%20%22KEY%20GRANT%22%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESince%20the%20formula%20searches%20only%20a%20single%20cell%2C%20the%20count%20will%20be%200%20or%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20advantage%20of%20the%20table%20is%20that%20the%20formula%20range%20extends%20as%20data%20is%20appended%20to%20the%20table.%3C%2FP%3E%3CP%3EOne%20less%20manual%20operation%20is%20one%20less%20opportunity%20for%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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 - 

 

example.PNG

 

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

@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

Highlighted

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

Highlighted

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