Very similar to this question: How to find and replace the character "*" in Excel

But I need to leave formulas untouched. I've got about 50+ sheets that have two types of cells with "*"

Case 1 contents - the value of the cell might be: "*** 1.43"

Case 2 contents - the value of the cell might be: "=100*B3"

I would like to find and replace all the Case 1 asterisks with "" while ignoring the Case 2 cells that contain formulas using asterisks as a multiplier. Basically, change the static cells but don't alter cells with formulas.



You can use the following routine if you have Excel 2013 or later. Place the code in the ThisWorkbook module. Run once; it will update appropriate cells across the entire workbook.

Sub SetStarsBlank()

    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.UsedRange
            If Not Application.WorksheetFunction.IsFormula(c) Then
                c.Value = Replace(c.Value, "*", "")
            End If

End Sub

First, make a copy of your file, just in case something goes wrong.

Then create the following VBA subroutine:

Sub StillLearning1()

    For Each c In Range("A1:Z100")
        If Not c.HasFormula Then
            temp = WorksheetFunction.Substitute(c.Value, "*", "")
            If Left(temp, 1) = "=" Or Left(temp, 1) = "'" Then
                temp = "'" & temp
            End If
            c.Value = temp
        End If
    Next c

End Sub

See How do I add VBA in MS Office? for general information on how to use VBA in Excel and the other Microsoft Office applications.  StillLearning1 is just an arbitrary subroutine name; use whatever name you want.  Change A1:Z100 to the range in which you want the replacement to occur.  I hope the rest is somewhat self-explanatory:

  • For Each c ... Next c is a loop that gets executed 2600 times (for the sample range of A1:Z100; 26 columns × 100 rows) with c going to every cell in that range.
  • c.HasFormula is a Boolean that tells whether cell c contains a formula.  This is slightly more reliable than checking whether the first character is =, because a cell that contains '=== is a string (text) value, but it will look like ===, and so it will appear to begin with =.

    If a cell contains a formula; leave it alone.  If it doesn't contain a formula, do the substitution.

  • Check the result of the substitution to see whether it begins with = or '.  If it does, assigning it directly to the cell will result in Excel interpreting that first character in an undesirable way, so protect it by prepending a '.

Then run the subroutine.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.