How to replace '0 with a 0?

[Guest]

I pull information in from an Essbase database. It puts missing information
in the cell as a text '0. I want to be able to replace '0 with a 0. Or how
would I set up my formula's to recognize the apostrophe?

=IF(C275=0,0,(+B275-C275)/C275)

So it doesn't come out like this? #DIV/0!

Thanks

 

[Guest]

If the column in which the '0 is appearing is in column A, then do =CLEAN(A1)
and fill down as necessary. Then copy and paste the values of that TRIM
function into column 1 and then re-run your formula below.

Dave

 

[Guest]

You can insert a helper column, and use the following if statement to
evaluate the cell data.
=IF(ISTEXT(A1),0,A1)

Then copy the helper column results and click EDIT in the menu and select
PASTE SPECIAL over your original values.

 

[Guest]

Unless you have a need for the apostrophe in the code Essbase uses for
missing data you can set this to a value of 0 in the Essbase Options dialog.
The result of a refresh should then be a plain 0, but it will be saved as
text.

This is easy to fix, however, by typing a 1 anywhere on your sheet, and copy
it. Now select the range that contains the 'zero' values and use the Paste
Special option on the Edit menu and select Multiply. When you press OK, the
text zeros will now be numeric, and no other values should have changed.
Afterwards, dont forget to delete the value of 1 that you copied.

HTH,

TK

 

[MyVeryOwnSelf]

I pull information in from an Essbase database. It puts missing

information in the cell as a text '0. I want to be able to replace '0
with a 0.

One way:

1. select an empty cell and
Edit > Copy

2. Select the range of cells to be affected and
Edit > Paste special > Add

This adds zero, so it leaves numbers unchanged but changes '0 to 0.

Or how would I set up my formula's to recognize the
apostrophe?

=IF(C275=0,0,(+B275-C275)/C275)

So it doesn't come out like this? #DIV/0!

One way:
=IF(N(C275)=0,0,(+B275-C275)/C275)

 

[Guest]

CareyJ - you can easily change the #MISSING to 0 that is not text by going
into the Essbase |Options |Display and using =0 to replace #MISSING.

Good luck!

 

[MyVeryOwnSelf]

+AD4- I pull information in from an Essbase database. It puts missing
+AD4- information in the cell as a text '0. I want to be able to replace '0
+AD4- with a 0. Or how would I set up my formula's to recognize the
+AD4- apostrophe?
+AD4-
+AD4- +AD0-IF(C275+AD0-0,0,(+-B275-C275)/C275)
+AD4-
+AD4- So it doesn't come out like this? +ACM-DIV/0+ACE-


One way is to select an empty cell and use
Edit +AD4- Copy

Then select a range of cells and use
Edit +AD4- Paste special +AD4- Add

The idea is that adding zero takes a number in text form and changes it to
a number in numerical form, but doesn+IBk-t change other stuff much. (If
there+IBk-s a formula, it adds +-0 at the end.)

 

[ucaq]

Replace text 0 with value 0

In the #Missing Label in the Essbase options use (0) instead of 0. This works with version 7.1.6 but I'm not sure if it works in earlier versions. It's worth a try.