First, make a copy of your file, just in case something goes wrong.
Then create the following VBA subroutine:
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
c.Value = temp
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.
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)
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
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.