Using IF,ISnumber and Find

Question

I am trying to use IF with ISnumber and Find but due to some error its not working in VBA and its to long also so thats why it is getting change.

Actual formula

  =IF(ISNUMBER(FIND("CS&L",C3)),"CS&L",IF(ISNUMBER(FIND("EMPLX",C3)),"Employee Cross Charges",IF(ISNUMBER(FIND("EOCINV",C3)),"EOC Specific Recharges (Inventory Related)",IF(ISNUMBER(FIND("EOCNINV",C3)),"EOC Specific Recharges (Non - Inventory Related)",IF(ISNUMBER(FIND("EXPAT",C3)),"Expats",IF(ISNUMBER(FIND("GLBSC",C3)),"Global Service Charges",IF(ISNUMBER(FIND("INFSY",C3)),"Information Systems",IF(ISNUMBER(FIND("TRDDL",C3)),"International Trade Deals",IF(ISNUMBER(FIND("IREXP",C3)),"Intra-Region Expats",IF(ISNUMBER(FIND("MGMTF",C3)),"Management Fees (Below OC)",IF(ISNUMBER(FIND("MANUF",C3)),"Manufacturing",IF(ISNUMBER(FIND("MARKT",C3)),"Marketing",IF(ISNUMBER(FIND("OVERH",C3)),"Overheads",IF(ISNUMBER(FIND("PROCUR",C3)),"Procurement",IF(ISNUMBER(FIND("PCTGR",C3)),"Product Category Reviews",IF(ISNUMBER(FIND("RD&Q",C3)),"RDQ",IF(ISNUMBER(FIND("RESTR",C3)),"Restructuring / Project",IF(ISNUMBER(FIND("ROYAL",C3)),"Royalties",IF(ISNUMBER(FIND("STRAT",C3)),"Strategy",IF(ISNUMBER(FIND("TRDMK",C3)),"Trademarks","Others"))))))))))))))))))))

After creating macro its getting change which is getting error

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B2").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(FIND(""CS&L"",R[1]C[1])),""CS&L"",IF(ISNUMBER(FIND(""EMPLX"",R[1]C[1])),""Employee Cross Charges"",IF(ISNUMBER(FIND(""EOCINV"",R[1]C[1])),""EOC Specific Recharges (Inventory Related)"",IF(ISNUMBER(FIND(""EOCNINV"",R[1]C[1])),""EOC Specific Recharges (Non - Inventory Related)"",IF(ISNUMBER(FIND(""EXPAT"",R[1]C[1])),""Expats"",IF(ISNUMBER(FIND(""GLBSC"",R" & _
        "),""Global Service Charges"",IF(ISNUMBER(FIND(""INFSY"",R[1]C[1])),""Information Systems"",IF(ISNUMBER(FIND(""TRDDL"",R[1]C[1])),""International Trade Deals"",IF(ISNUMBER(FIND(""IREXP"",R[1]C[1])),""Intra-Region Expats"",IF(ISNUMBER(FIND(""MGMTF"",R[1]C[1])),""Management Fees (Below OC)"",IF(ISNUMBER(FIND(""MANUF"",R[1]C[1])),""Manufacturing"",IF(ISNUMBER(FIND(""MAR" & _
        "C[1])),""Marketing"",IF(ISNUMBER(FIND(""OVERH"",R[1]C[1])),""Overheads"",IF(ISNUMBER(FIND(""PROCUR"",R[1]C[1])),""Procurement"",IF(ISNUMBER(FIND(""PCTGR"",R[1]C[1])),""Product Category Reviews"",IF(ISNUMBER(FIND(""RD&Q"",R[1]C[1])),""RDQ"",IF(ISNUMBER(FIND(""RESTR"",R[1]C[1])),""Restructuring / Project"",IF(ISNUMBER(FIND(""ROYAL"",R[1]C[1])),""Royalties"",IF(ISNUMBE" & _
        "STRAT"",R[1]C[1])),""Strategy"",IF(ISNUMBER(FIND(""TRDMK"",R[1]C[1])),""Trademarks"",""Others""))))))))))))))))))))"
    Range("B3").Select
End Sub

Answer 1

Some points to note:

1. Long formulas are difficult to read, I suggest to use [Alt] + [Enter] keys combined to start a new line in the same cell thus breaking the formula in several lines (see fig. 1).

2. FIND is case sensitive, instead use SEARCH (see fig. 1).

3. It seems that you try record a macro to get the formula as VBA, be aware that very long formulas are not properly recorded into VBA. Nevertheless you can use the procedure below to print the entire formula of the ActiveCell in the Visual Basic Immediate Window.

   Sub Get_Formula()
   Rem Opens Immediate Window and Clears it
   SendKeys "^g^a{DEL}": Stop
   
   With ActiveCell
       Debug.Print vbLf; vbLf; String(131, "*")
       Debug.Print "Formula in Wbk\Wsh\Cell: "; .Parent.Parent.Name; " \ "; .Parent.Name; " \ "; .Address(0, 0)
       Debug.Print ActiveCell.Formula
       Debug.Print vbLf; String(131, "*"); vbLf
   End With
   SendKeys "^g^{HOME}" ': Stop
   End Sub
   

As regards the formula, there are several solutions, I will list three of them.

These formulas assume the list of strings to associate with the corresponding description is located in B7:B28

1. Standard Excel Formula: Using a standard long formula like the one you posted, just replace FIND with SEARCH as the target word could be in lower or upper case (see Fig 1). The issue with long formulas is that they are difficult to read and required heavy maintenance. Enter this formula in C7 and copy till last record (see Fig. 1).

   =IF(ISNUMBER(SEARCH("CS&L",$B7)),"CS&L",
   IF(ISNUMBER(SEARCH("EMPLX",$B7)),"Employee Cross Charges",
   IF(ISNUMBER(SEARCH("EOCINV",$B7)),"EOC Specific Recharges (Inventory Related)",
   IF(ISNUMBER(SEARCH("EOCNINV",$B7)),"EOC Specific Recharges (Non - Inventory Related)",
   IF(ISNUMBER(SEARCH("EXPAT",$B7)),"Expats",
   IF(ISNUMBER(SEARCH("GLBSC",$B7)),"Global Service Charges",
   IF(ISNUMBER(SEARCH("INFSY",$B7)),"Information Systems",
   IF(ISNUMBER(SEARCH("TRDDL",$B7)),"International Trade Deals",
   IF(ISNUMBER(SEARCH("IREXP",$B7)),"Intra-Region Expats",
   IF(ISNUMBER(SEARCH("MGMTF",$B7)),"Management Fees (Below OC)",
   IF(ISNUMBER(SEARCH("MANUF",$B7)),"Manufacturing",
   IF(ISNUMBER(SEARCH("MARKT",$B7)),"Marketing",
   IF(ISNUMBER(SEARCH("OVERH",$B7)),"Overheads",
   IF(ISNUMBER(SEARCH("PROCUR",$B7)),"Procurement",
   IF(ISNUMBER(SEARCH("PCTGR",$B7)),"Product Category Reviews",
   IF(ISNUMBER(SEARCH("RD&Q",$B7)),"RDQ",
   IF(ISNUMBER(SEARCH("RESTR",$B7)),"Restructuring / Project",
   IF(ISNUMBER(SEARCH("ROYAL",$B7)),"Royalties",
   IF(ISNUMBER(SEARCH("STRAT",$B7)),"Strategy",
   IF(ISNUMBER(SEARCH("TRDMK",$B7)),"Trademarks","Others"))))))))))))))))))))
   

   

Fig. 1

2. FormulaArray: For data like the one you are using the best practice is to create a table to hold the relationship between the different elements (i.e Short Description to Description in this case) as mentioned by user1016274. However, I would specifically suggest to create an Excel Table (ListObject in VBA) due to the advantage of using its structured references in formulas and VBA (see Use structured references in Excel table formulas)

   In this case I created an Excel Table in a separated worksheet, so it does not cause any disruption to the rest of the reports and data you might have in the workbook. The Excel Table is named tDescriptions (see fig. 2a).

   

   Fig. 2a

   Then we use a FormulaArray to retrieve the associated Description from the table. Enter this FormulaArray in D7 and copy till last record (see Fig 2b).

   =IF(SUM(1*NOT(ISERR(SEARCH(tDescriptions[Short.Desc],ShortLongDesc!$B7))))=0,"Others",
   INDEX(tDescriptions[Description],
   SUM((1*NOT(ISERR(SEARCH(tDescriptions[Short.Desc],ShortLongDesc!$B7))))
   *(ROW(tDescriptions)-ROW(tDescriptions[#Headers])))))
   

   

   Fig. 2b

3. User Defined Function (UDF): Although we can use an Array to hold the Short Description to Description relationship, I prefer to keep it in the Excel Table created in the previous point as it's easier to maintain. Enter this formula in E7. (see Fig 3)

   =Get_LongDescription(B7)
   

   

   Fig. 3

   Below is the code of the UDF. The argument entered can be a string or one cell reference, if entered a range with more than one cell the UDF will return an error.

   Option Explicit
   
   Public Function Get_LongDescription(sText As String) As String
   Dim Lob As ListObject
   Dim lRow As Long
       Rem Set Default Result
       Get_LongDescription = "Others"
       Rem Set Objects
       Set Lob = ThisWorkbook.Sheets("ShortLongDesc(Tbl)").ListObjects("tDescriptions")
       Rem Search SHort Desc in Text
       With Lob
           For lRow = 1 To .DataBodyRange.Rows.Count
               If InStr(LCase(sText), LCase(.ListColumns("Short.Desc").DataBodyRange.Cells(lRow).Value2)) <> 0 Then
                   Get_LongDescription = .ListColumns("Description").DataBodyRange.Cells(lRow).Value2
                   Exit For
       End If: Next: End With
       End Function
   

Answer 2

You can well keep using a worksheet function for this. The formula just translates tokens/abbreviations into longer terms, or "other" if unknown. I would separate the translation formula from the data like this:
1) create a 2-column table somewhere on that sheet, with column 1 holding tokens and column 2 the corresponding term:

CS&L    CS&L
EMPLX   Employee Cross Charges
EOCINV  EOC Specifiic Recharges (Inventory Related)

For ease of use, give that range (say, X1:Y3) a name like "terms".
2) Then, in your table, use a formula which looks up a short token and returns the longer term - VLOOKUP():

=IF(ISNV(VLOOKUP(C3,terms,2,FALSE)),"other",VLOOKUP(C3,terms,2,FALSE))

It looks a bit convoluted because it needs to evaluate the lookup twice to check for an unknown token. But, in contrast to your first formula, there is no data included in the formula itself. Neither is the size of the translation table restricted.

addendum:
If the short terms are not the only text in the cell then the above formula will return an error all the time; it only compares the whole cell's contents (C3). I think that's what you mention in the comment.

In order to mimick FIND completely use this:

={INDEX(longterms,MAX(MAX(IF(ISERROR(SEARCH(shortterms,C3)),0,1)*ROW(shortterms)),MIN(ROW(shortterms)))-ROW(longterms)+1)}

Enter this as a matrix formula (enter with Ctrl-Shift-Enter).
Here, I use SEARCH to find text without looking at the case - you can use FIND instead if you want to take the case into account.
The innermost SEARCH creates an array of row numbers (if term is found) or 0 (if not found) of the shortterm named range.
MAXcreates a single value from that array.
This row number is taken as an INDEX into the named range longterms which is adjacent to shortterms.
The MIN function is only needed to change the value 0 (if a term is not found) into the row number of the first entry of longterms - which needs to be the "other" text. So the 2 named ranges look like:

U7: (empty) V7: other
U8: EMPLX   V8: Employee Cross Charges
U9: EOCINV  V9: EOC Specifiic Recharges (Inventory Related)

Define shortterms as $U$7:$U$9 and longterms as $V$7:$V$9 .