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

Question

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!

LtCdr Data · Accepted Answer · 2015-09-06T00:49:25.543

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

score 2 · Answer 2 · answered Feb 13 '20 at 00:39

You can use a formula-based approach if you wish.

It hinges on the EVALUATE() function which will not work in cell formulas, but WILL work in a Named Range. EVERYONE can do this, EVERY version of Excel. Just type "=EVALUATE(" in a cell and it will look like it isn't available, but do it right, in a Named Range and it's good.

So create two Named Ranges, one for each condition. Call one "Formula" and create a Named Range with this in the "Refers to" box:

=EVALUATE(FORMULATEXT(Sheet1!A1))

and create another Named Range called "NotFormula" giving it this in the "Refers to" box:

=EVALUATE(SUBSTITUTE(Sheet1!A1,"*",""))

(If you select the cell next to the cell to work on, A1 assumed here, so park yourself in B1 while creating those Named Ranges which, if you'll notice, use relative referencing. Choose where to be parked based on where the helper column will exist.)

Then use a "helper column" (etiher right there in the spreadsheets by the inputs, or on another sheet, even one added for this and then deleted. Enter the following formula in it:

=IF(ISFORMULA(A1),Formula,NotFormula)

If it IS a formula, then Formula is the result. If it is not, then `NotFormula is the result.

Formula gets the result of FORMULATEXT() and evaluates it, turning the text string it gives into an actual formula, at least in result if not "technically" so. It's the only non-macro way I know of achieving that.

NotFormula simply uses SUBSTITUTE() to replace the asterisks. No need for the Find and Replace requirement of putting a tilde before the asterisk. Bear in mind that this process would produce a string in your example since the "1.43" would end up preceded by a space, but this method will make it a real boy, I mean a real number. Might be what you want, might not. If not, in the Named Range, wrap the function in a TEXT() function to preserve that space.

So, has to have a helper column. That means that if you do not want original and result side by side, but rather replacing the original, you'd need to copy and paste values over the original data, then delete the helper column. If you DO want side by side result and original, you're gold already.

Remember though, you DO have EVALUATE() available, you just can't use it in cells. You have to use it inside a Named Range.

Also, they have been saying for 27 years it could go away. If the "27 years" part does not convince you it is future-safe to use, there are some signs that other things depend on the old Excel 4 macro functionality, what underlaid those functions, not the functions themselves, and that Excel can never really ever get rid of them. So...

score 2 · Answer 3 · edited Mar 20 '17 at 10:16

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.

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

3 Answers3