Another of Excel's idiosyncrasis

B

Buce

In the formula "=ROUND(Data!G242*0.975*0.5*0.975,0)", if I need to do an
edit, replace to eliminate the portion "*.975,0)" and replace that with
",0)", Excel insists on screwing up the whole formula and resulting cell
entry is ",0)"

This is so stupid of Excel to do it that way, when I am only wanting a
portion replaced. How do I get around this most inconvenient indiosyncracy of
Excel in this case?

Thanks,
 
H

Harlan Grove

Buce wrote...
In the formula "=ROUND(Data!G242*0.975*0.5*0.975,0)", if I need to do an
edit, replace to eliminate the portion "*.975,0)" and replace that with
",0)", Excel insists on screwing up the whole formula and resulting cell
entry is ",0)"

This is so stupid of Excel to do it that way, when I am only wanting a
portion replaced. How do I get around this most inconvenient indiosyncracy of
Excel in this case?
Click to expand...

* and ? are wildcard characters, * matching any text, ? matching any
single character. If you try to find

*.975,0)

the * will match all characters to the left of .975,0), so you're
matching the entire formula. You need to use ~* to represent a literal
asterisk. So search for

~*0.975,0)

[note: you need to include the 0 to the left of the decimal place].
 
G

Gord Dibben

Buce

Not an idiosyncracy, but a question of search wildcards.

The asterisk "*" is a wildcard in a find and replace.

To find and replace an * you must preface with a tilde(~)

Try finding "~*0.975,0)" and replacing with ",0"

Same for finding a ? mark. "~?" must be used.


Gord Dibben Excel MVP
 
B

Bruce

Thanks, I should of remembered things like that, but I run into that
situation to infrequently to remember I guess.

Bruce

Harlan Grove said:
Buce wrote...
In the formula "=ROUND(Data!G242*0.975*0.5*0.975,0)", if I need to do an
edit, replace to eliminate the portion "*.975,0)" and replace that with
",0)", Excel insists on screwing up the whole formula and resulting cell
entry is ",0)"

This is so stupid of Excel to do it that way, when I am only wanting a
portion replaced. How do I get around this most inconvenient indiosyncracy of
Excel in this case?
Click to expand...

* and ? are wildcard characters, * matching any text, ? matching any
single character. If you try to find

*.975,0)

the * will match all characters to the left of .975,0), so you're
matching the entire formula. You need to use ~* to represent a literal
asterisk. So search for

~*0.975,0)

[note: you need to include the 0 to the left of the decimal place].
Click to expand...
 
Top