none
Excel VBA remove everything between 2 characters in formula RRS feed

  • Question

  • Hi All,

    I am copying one sheet to new workbook due to which i am getting refernce to old workbook in formula in new workbook. for e.g =D129/(D111/VLOOKUP(TempSht!C130,'[oldwb.xlsm]validationListSht'!J3:K4,3,FALSE)

    Using vba code i want to remove everything between and including square brackets [ ]. So resul shall look like

    =D129/(D111/VLOOKUP(TempSht!C130,validationListSht!J3:K4,3,FALSE)

    Thanks,

    Zav


    • Edited by zaveri cc Wednesday, December 23, 2015 9:54 PM
    Wednesday, December 23, 2015 9:54 PM

Answers

  • >>>Using vba code i want to remove everything between and including square brackets [ ]. So resul shall look like

    According to your description, you could use Range.Formula Property (Excel) to return the formula as a string, then you could use String Function to remove special characters.
    You could refer to below code:

    Dim strFormula As String
    Dim sSpecialChars  As String
     
    sSpecialChars = "[]"   
    strFormula = ActiveCell.Formula
       
    For i = 1 To Len(sSpecialChars)
        strFormula = Replace(strFormula, Mid$(sSpecialChars, i, 1), "")
    Next
    
    ActiveCell.Formula = strFormula
    

    For more information, click here to refer about Range.Formula Property (Excel) and here to refer about String Functions (Visual Basic)

    • Proposed as answer by David_JunFeng Friday, December 25, 2015 1:03 AM
    • Marked as answer by David_JunFeng Tuesday, January 5, 2016 1:50 AM
    Thursday, December 24, 2015 3:25 AM

All replies

  • you could do something with just a replace (ctrl + H) in excel.  Just replace [*] with a null string and it will get rid of all your irrelevant brackets and everything in between them.  Just make sure it to tell it to look in formulas if you havent.

    select the whole column you want to elim brackets from if you have brackets you want to save outside of it.
    • Edited by Myysterio Wednesday, December 23, 2015 9:58 PM
    Wednesday, December 23, 2015 9:58 PM
  • >>>Using vba code i want to remove everything between and including square brackets [ ]. So resul shall look like

    According to your description, you could use Range.Formula Property (Excel) to return the formula as a string, then you could use String Function to remove special characters.
    You could refer to below code:

    Dim strFormula As String
    Dim sSpecialChars  As String
     
    sSpecialChars = "[]"   
    strFormula = ActiveCell.Formula
       
    For i = 1 To Len(sSpecialChars)
        strFormula = Replace(strFormula, Mid$(sSpecialChars, i, 1), "")
    Next
    
    ActiveCell.Formula = strFormula
    

    For more information, click here to refer about Range.Formula Property (Excel) and here to refer about String Functions (Visual Basic)

    • Proposed as answer by David_JunFeng Friday, December 25, 2015 1:03 AM
    • Marked as answer by David_JunFeng Tuesday, January 5, 2016 1:50 AM
    Thursday, December 24, 2015 3:25 AM