Another of Excel's idiosyncrasis

[Guest]

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,

 

[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?

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

 

[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

 

[Guest]

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

Bruce

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?

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