zeros

  1. I

    leading zeros with both numbers and letters in string

    I need to add leading zeros before two separate strings for all in column E. For example: <colgroup><col width="111"></colgroup><tbody> 0336565-CM </tbody> <colgroup><col></colgroup><tbody> 336700 </tbody>
  2. C

    countifs blank zeros.

    Have spent the last two hours losing the plot trying to find why a countifs is returning unexpected result, then discovered that countifs treats blanks and zeros the same... One of the columns i am using in the countifs has both blanks and zeros that i need to differentiate. How would you do...
  3. C

    countifs blank zeros.

    Have spent the last two hours losing the plot trying to find why a countifs is returning unexpected result, then discovered that countifs treats blanks and zeros the same... One of the columns i am using in the countifs has both blanks and zeros that i need to differentiate. How would you do...
  4. S

    Delete (Clear) Zero values only wita VBA

    Hi All, I have database (about 100 rows & 20 columns) and they contains just 0 (zeros) and different numbers, including numbers with zeros, for example 60. I would like to delete (clear) numbers which has zero values only, not zero from 60, which will change its value from 60 to 6. Is there any...
  5. C

    Formula to count the number of sets of 7-consecutive-zero's in a row of data

    I have a database with 300 rows and 50 columns. The cells are populated with either zero, one or blank. I'm looking for a formula that I can plug into column number 51 for the 300 rows, that will tell me how many sets of 7-consecutive-zero's there are in each row.
  6. D

    VBA NumberFormat

    is there a way to combine the following two commands in one? Sums_range.NumberFormat = "[>=1000]#'##0;0" Sums_range.NumberFormat = "0;-0;-" the first formats numbers in the 0'000 format (with tone for thousands) and no decimals the second formats zeros to dashes
  7. L

    Keeping leading zeros while copying to new worksheet

    Hi all! Another day, another problem.. Here is what's going on: I have an excel where there are two sheets. Sheet 1 contains Phone numbers in Column X in the following format: 050-7080-6030. I need to copy this to Sheet 2 while at the same time removing "-" (I achieved this by creating a table...
  8. R

    zero returns

    I have an array formula which works and returns the correct value, as in the correct number. This result is a precedent for further calculations or formulae. My ongoing formulae result in a zero because the original answer is returned as text. I have checked cell formats to be general or number...
  9. S

    Harmonic Mean to EXCLUDE zeros or missing data

    Hello again, I am trying to using harmonic mean on a range that has missing data or zeros. Basically, weekly sales numbers that have yet to be entered for the year that I do on a weekly basis. I have many different formulas set in place already that work great, but I need to find the best way...
  10. L

    hide 0 values in excel chart

    hello. i need help on hiding the 0 values in excel chart. ive found things like "#" in format code but it rounded up my figures. i have data like 1.7 or 6.5 and it rounded up. is there a way to hide 0s in the series without changing the other figures in the series? :( :confused:
  11. E

    clean data, remove rows of #N/A and zeros

    Hi, so I have large workbooks that contain many sheets each with many rows of data. lots of these rows are either entirely #N/As or entirely zero, and I would like to remove these. I have some code that sort of does it: Dim ws As Worksheet Application.ScreenUpdating = False...
  12. C

    Leading Zeros insert

    Hi I am new to this board and have tried every solution to inserting leading zeros. My question is how to insert leading zeros to the following cell entry? 206A 206B 206C 206T I have tried the format function but this does not work due to the fact I have a letter on the end of the cell...
  13. B

    VBA- Recognize periods (.) as 0s??

    Hi all, Is there anyway to make VBA recognize periods as 0s. I have a macro that color codes an automatically generated report, but sometimes 0's show up normally and sometimes as periods (.). This makes rows that should appear green (CC: 3407718) not do so. Here is my current code: i = 2...
  14. N

    Apply a macro only to highlighted cells

    I have data in columns that I want to create charts for. However, this data is dynamic and the number of cells where there is data varies. The rest of the cells that don't have numbers have 0s, however if I applied a macro to the whole column all of the zeros would show up in the resulting...
  15. C

    FIX formula to remove the #DIV/0! that is feeding zeros into Data Table Chart

    Trying to fix this formula to remove the showing of: "#DIV/0!" but not having luck.... =((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91) Ultimately, this is occuring in YTD (yr to date) tables that are awaiting input for future months that have not taken place yet.... These #DIVS...
  16. F

    Construct a list that excludes 0 (from 0 0 0 1 2 3 to 1 2 3)

    Hi I am trying to do a thing automatically. It's part of a more global automatization. I have a range with the values 0 0 0 0 1 2 3 0 0 0 and I want, on the right, to get the list of 1 2 3 appearing. Please look at the picture for an example of what I would need to do. Do you have any...
  17. MarkCBB

    Need a faster Code to delete Zeros

    Hi there, I am currently using the following code to delete zeros from a selection, however it is really slow. if there a different way of doing this? Below is the code that I am currently using: Sub Mytest() Dim i As Integer Dim Cnt0 As Integer i = 0 Cnt0 =...
  18. C

    Chart question

    I created a pivot chart in Excel 2007 and all was fine. Then when I copied the chart sheet and data sheet to another workbook, the chart displayed all nulls as zeros and the line chart looked awful. I discovered that the data for the chart series changed from a RANGE to an ARRAY, and that was...

Some videos you may like

This Week's Hot Topics

  • Launch Script When Tab Is Opened
    Hello all! Is there a way to launch a script or message every time a particular tab (DATA1) is selected/opened? I know it can be done when...
  • VBA that can extract words that starts with # and ends with 2021.
    Hi Everyone, I am straggling to in searching for a vba code that will extract a string that starts with # and ends with 2021. The string has no...
  • Date update overflow error ‘6’
    Dear MrExcel community, I have a project search & update & add & delete can do. There are 32 column and 5 of them is date. When I search date...
  • VLOOKUP Formula ?
    Hi i am using this below but i need to return Column C on Detailed_view. You can see ,1 this returns D, but i need to return C before D. I tried...
  • conditional formatting the whole row
    Hello This is a question solved earlier by etaf. But I am getting some problem. I need help to correct the formula as it is not working in some...
  • Vlookup in vba
    Hi, I am new with the VBA, can someone help me, I have made this macro, but the loop across files in a directory runs only in cell G13, and I...

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