Excel – How to find and replace the character “*” in excel text values in cell, but NOT formulas

microsoft excelmicrosoft-excel-2010microsoft-excel-2013vbaworksheet-function

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.

Thanks!

Best Answer

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
        Next
    Next

End Sub
Related Question