Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Edit / Replace the ' * ' character in Excel

Jun 30, 2005 2:36AM PDT

I have a formula in Excel 2002, (well several '000 of them) which begin =12* (cell reference) and I want to edit replace =12* with =

The * character is causing me problems, how can I do this?

Discussion is locked

- Collapse -
Re: edit/replace in Excel
Jun 30, 2005 4:08AM PDT

Rcgyuk,

Indeed I get a rather strange error message if I try this. But there is an obvious work-around for your problem, that works flawlessly with me: replace =12 with =1

You'll immediately recognize that =1*A1 equals =A1, as long as A1 contains a number.

Let me note a good Excel design principle: don't use constants, but use absolute cell reference. So don't write =12*A1 but =$Z$1*A1 and put 12 in Z1. Then the whole operation would have been trivial: replace the 12 in Z1 with 1.

Kees

- Collapse -
It's because
Jun 30, 2005 5:46AM PDT

the * denotes a wild card I believe.

When using Find " some-entry* ", the star looks for all instances of " some-entry " and whatever comes after.

So Find " =12* " looks for all " =12*(cell references) ", and Replace replaces them with just an = sign.

Good suggestions by Kees.

Mark

- Collapse -
Re: edit/replace in Excel
Jun 30, 2005 6:09PM PDT

The reason I can't replace *12 with *1 is that I need to copy these formulas and point them to a different column, but the same line and row, and what I am bringing back is a 5 character alphanumeric code, not a number, so muliplying by 12 causes an error Sad

I thought there was a way to edit replace wild cards, like a double quote or something, but I can't make it work

- Collapse -
Alternatives:
Jun 30, 2005 8:00PM PDT

1. Change one formula by hand, then drag down with fill handle (assuming there is some regularity in the location of the formula and location of the field pointed to).
2. Write a macro to loop through all the relevant fields and change the formula.

Kees

- Collapse -
The simple and correct answer is...
Jun 30, 2005 11:04PM PDT

Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal. When you use Find or Replace to replace or search for an asterisk (*), a question mark (?), or a tilde (~), you must precede the character with a tilde (~).

So in your case...

in the 'Find what:' box, type =12~*
in the 'Replace with:' box, type =

- Collapse -
It Worked !!
Jul 1, 2005 1:32AM PDT

That was exactly what I was looking for, I knew there was a way to do it, but couldn't find it.

Thanks