none
How to remove part of formula from many cells based on character numbers? RRS feed

  • Question

  • How to remove parts of a formula from many cells (B41:N62)

    The formula is not the same for each cell, but follows the same system. I want to remove the characters in bold & underline.

    ='90'!$G$8+'90'!$G$17+'90'!$G$13+'90'!$G$15+'90'!$G$23+'90'!$G$27+'90'!$G$43+'90'!$G$53+'90'!$G$49+'90'!$G$67+'90'!$G$65+'90'!$G$75+'90'!$G$79+'90'!$G$81

    The character numbers I want to remove is (from left):

    • 23-45
    • 90-111
    • 133-154

    Friday, November 16, 2012 3:09 PM

Answers

  • Are you sure the character numbers will always be the same? If so:

    Sub ReduceFormulas()
        Dim rng As Range
        Application.Calculation = xlCalculationManual
        For Each rng In Range("B41:N62")
            If rng.HasFormula Then
                rng.Formula = Left(rng.Formula, 22) & Mid(rng.Formula, 45, 44) & _
                    Mid(rng.Formula, 111, 21)
            End If
        Next rng
        Application.Calculation = xlCalculationAutomatic
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by PaulAakerøy Friday, November 16, 2012 5:29 PM
    Friday, November 16, 2012 3:28 PM