Forum Discussion

Belinea2010's avatar
Belinea2010
Copper Contributor
Apr 11, 2021
Solved

Help to modify a SUBSTITUTE(MID(LEFT formula so it works as expected.

The Excel formula below looks at Cell A1 in a worksheet called FolderDataImport. It searches the data in Cell A1 and locates the “-“ character, of which there is only ever one, and then looks for th...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 11, 2021

    Belinea2010 

    Try

     

    TWs.Range("D2:D" & Lr + 1).Formula = "=IF(ISNUMBER(FIND(""["",FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)+1)),MID(FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)-FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)-1),"""")"

HansVogelaar's avatar
HansVogelaar
MVP
Apr 11, 2021

Belinea2010 

Try

 

TWs.Range("D2:D" & Lr + 1).Formula = "=IF(ISNUMBER(FIND(""["",FolderDataImport!A1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)+1)),MID(FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1,FIND("")"",FolderDataImport!A1,FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)+1)-FIND(""("",FolderDataImport!A1,FIND(""-"",FolderDataImport!A1)+1)-1),"""")"

  • Belinea2010's avatar
    Belinea2010
    Copper Contributor
    Apr 11, 2021
    Hi Hans.

    Thank you for your kind reply and code.
    It works perfectly and you have really helped out.

    Thanks Again.

Resources