Office & Productivity Software forum

General discussion

Edit / Replace the ' * ' character in Excel

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
You are posting a reply to: Edit / Replace the ' * ' character in Excel
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Edit / Replace the ' * ' character in Excel
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: edit/replace in Excel

In reply to: Edit / Replace the ' * ' character in Excel

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

In reply to: Re: edit/replace in Excel

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

In reply to: Re: edit/replace in Excel

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:

In reply to: Re: edit/replace in Excel

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

In reply to: Edit / Replace the ' * ' character in Excel

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

In reply to: The simple and correct answer is...

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

Thanks

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

DEALS, DEALS, DEALS!

Best Black Friday Deals

CNET editors are busy culling the list and highlighting what we think are the best deals out there this holiday season.