=text remove 0.00

GoodyCC

New Member
Joined
Jun 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Excel.jpg
    Excel.jpg
    30.5 KB · Views: 2

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=TEXT(A2,"0.00")
use this:
Excel Formula:
=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".
 
Solution

GoodyCC

New Member
Joined
Jun 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Excel 2.jpg
    Excel 2.jpg
    50.6 KB · Views: 1

GoodyCC

New Member
Joined
Jun 11, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,334
Office Version
  1. 365
Platform
  1. Windows
You are welcome
Glad it worked out for you!
 

Forum statistics

Threads
1,136,734
Messages
5,677,455
Members
419,693
Latest member
divtjd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top