Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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 -- Brevity is the soul of wit. "CareyJ" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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. -- Kevin Backmann "Dave F" wrote: 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 -- Brevity is the soul of wit. "CareyJ" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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 "CareyJ" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
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! "CareyJ" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to replace '0 with a 0?
+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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula in Excel / Replace numbers with 'words' | Excel Discussion (Misc queries) | |||
Excel: Add replace within selection functionality | Excel Discussion (Misc queries) | |||
replace value in excel? | Excel Worksheet Functions | |||
Search and replace | Excel Worksheet Functions | |||
replace absolute references | Excel Worksheet Functions |