=text remove 0.00

[GoodyCC]

Hey,

Fairly new to this so any help is appreciated.

We use excel to run graphics on a OBS System for a money exchange company - issue being OBS only likes to use "text" formatting, not number, currency or even general. We show a lot of currency on the the screen that we copy from various sources.

I have sheet set up so that if we copy data from a source it will pull the data in to the sheet then run an =TEXT(XX,"0.00") formula that results in a number stored as text 2 digit number. then a Macro will copy and paste Values into the main spreadsheet. However I need to remove the cells that only result in 0.00 as in there is no data in the source.

So is there some sort of code that equates to "if cell A2 has data in then =Text the cell in 0.00 Format"

I've tried find and replace but it doesn't recognise 0.00 and I've tried advanced settings and removing "show a Zero in cells that have zero value"

Thanks

 

[Joe4]

Welcome to the Board!

I would just modify your formula so that it does not format blank entries to "0.00".

You can do that by instead of using this formula:

=TEXT(A2,"0.00")

use this:

=IF(A2<>"",TEXT(A2,"0.00"),"")

If it is already too late and you have to clean up files that are already like this, simply select all the data, and go to Find/Replace and replace 0.00 with nothing.
Just be sure to click "Options" on the "Replace" tab and make sure that the "Match entire cells contents" check box is checked.
This will ensure to only replace cells that are exactly "0.00", and not any that are like "70.00".

 

[GoodyCC]

Hey Joe4

Thanks for responding so quickly!

I've been tasked with creating a new spreadsheet from scratch so I have no files to clean up thank goodness. The Find/Replace option does work which I can run on the main sheet as a macro if necessary (now I remembered to check the match entire cell contents box is ticked), but I really interested in If formula that you have provided although it just doesn't seem to work on my Sheet for some reason

 

[GoodyCC]

Hey

Got it working now i think! Cell was formatted as text ?‍ I didn't work as originally thought as the blank column is column A were not actually blankit had a formula in to pull through from a different sheet. by changing the cell value from "a2" to Sheet1!A2 it worked a treat!

Really appreciate your help no doubt i will be back on here with more questions soon

Thanks

 

[Joe4]

You are welcome
Glad it worked out for you!