1. H

    Replacing zeros with boundary numbers

    Hello, I have rows of numbers as shown in the following examples: 1 0 0 0 1 0 0 0 0 0 0 0 2 0 0 2 0 0 3 0 3 0 0 0 0 0 0 I want to come up with a dynamic formula to fill zeros with boundary numbers. The result should be following: 1 1 1 1 1 0 0 0 0 0 0 0 2 2 2 2 0 0 3 3 3 0 0 0 0 0 0 I...
  2. F

    How we can remove zeros before numbers?

    Hi, I need help to solve this issue, Like i have to many customer codes and they all have zeros before numbers like, <colgroup><col><col></colgroup><tbody> Current Shape Required 000003 3 PAK LAND CHEMIST 000005 5 KHALID MEDICAL STORE 000006 6 CHISHTI MEDICAL STORE 000025...
  3. sharky12345

    Extract number from string but retain leading zeros

    I want to extract the numbers from a cell containing a mixed string but I need to retain the leading zeros. My data consists of the following format: SD001, SD002 through to SD1790 There will always be the text 'SD' followed by 3 or 4 numerical digits and it's the numerics I need, but...
  4. O

    Vlookup retaining leading zeros

    I have a pretty log winded spreadsheet that's over complicated but other than this vlookup, it works so please, any assistance would be greatly appreciated. My formula is =VLOOKUP(A4,Names!$A$9:$F$300,5) A4 contains number 1 My list "Names" looks something like this <tbody> 1 10449 Blogs...
  5. xeven_

    Dividing in excel 2016

    I want to divide a long string/column of large numbers by 10. For example 280875008 is in E1. When I put =10/E1 it is giving me 3.56E-08. I do not want the E-08. When I go to format cells and change it from general to number it is turning my values into zeros, which I also do not want. Is there...
  6. Y

    return numbers

    hey, I have a column that has zeros from top to bottom(T5: T100). It gets filled up every day by me with different numbers, some positive & some negative. I have a different column that I want to fill with all the numbers that are different than 0(AB30: AB60). for example(T5: T100) is: day 1 -...
  7. L

    accounting vs currency number formatting

    Hi I was reading one of Mike Girvin's document and he said the following: ++ Accounting Number Format: Fixed dollar sign (left edge of cell). Negatives are in parenthesis. Zeros are dashes. Decimals always line up. Currency: Floating dollar sign. You choose how to show...
  8. F

    VBA - If Consecutive Zeros in an Array

    I have written some VBA code that randomly generates a 1 or 0 depending on a probability. It then stores these into an array, which is defined by the the number of data points I have (rows) and by the number of iterations the user wants to run through (columns).I have transposed the data into...
  9. D

    Data validation restricted to text lenght to 8 - but to include any leading zeros

    Hi I am trying to ensure that a range of excel cells shows exactly 8 digits. This part is appears to be easy by using Data Validation to set Text lenght to equal to 8. However as some of the numbers include one or two leading zeros which I need to show this data validation shows it as an error...
  10. K

    Counting cells with number

    I have issue in count cells which I hope you can suggest a solution for. I have column with a mix of 0s and value >0. So I need to count number of zeros in between each number>0 and state it on the next column. It looks like this and what I want to achieve: 1 0 0 0 0 12 count= 4 0 0 0 3...
  11. RobbieC

    Showing 0.00 in a single cell

    Hi there, I have a sheet where I have disabled 'show zero in cell where cell value is zero' in advanced options... However I have one cell where I would like 0.00 to appear. The cell is formatted as a number to 2dp If I re-enable the advanced setting to show zeros, the entire sheet fills with...
  12. L

    Leading Zeros

    Hello, I'm using Excel 2016. I need to change numbers in the coulmns to include leading zeros. So 1 will become 001, 2 will become 002, etc. Then I want to save it as a txt file. I changed my column to text and made sure in the number section it says text. When I do a replace from 1 to 001, it...
  13. Sean15

    How to retain preceding zeros when converting number stored as text

    Hello: I have a list of numbers in column C stored as text and left indent. Some of the numbers have preceding zeros. e.g. 000111, 01212, 001451, 12151, 0000001245 When I convert column C to numbers, Excel loses the preceding zeros and right indents. How can I convert numbers stored as text...
  14. C

    Moving Average

    Hi All, Looking for a little help with the following please, I want to find the average according to the variable in the "Average Weeks" cell. So in this example it would look at the last 3 weeks figures, Weeks 4 - 6 and work out the average = 9243 As in the example, the future cells Week...
  15. L

    using 0 instead of # in custom number formatting

    Hi I just watched a video on youtube about number formatting. I took screen shot of all the codes he was using, please see below. He only used 0 instead of #. I thought that is good idea because with 0 you can represent both the number and the zeros while with # you would only represent number...
  16. B

    Concatenating cells with differing leading zeros

    I have a spreadsheet where i have four columns (B, D, E &F) which hold info which i need to concatenate to create a unique reference number. The issue i have is that the first column contains text, the second & third contain two digits and the fourth contains three. The second, third & fourth...
  17. M

    Add apostrophe to a number with leading zero's - but keep leading zero's

    Within a range (column, containing both text and numbers) I have numbers displayed like: 0001 0010 etc. Cell properties -> custom show: 0000 I would like to add an apostrophe to those cells. However .. the result should still be that the leading zero's are displayed, i.e. the result would...
  18. C

    Adding trailing zeros to rounding formula containing text

    Hello. I am using the following formula: =$G$5&ROUND(F134/F25,2)& "/eqv.". I am wanting the output to show zeros in the hundredths place instead of being dropped by Excel's default function. Right now my output is "0.4/eqv." but I would like for it to be "0.40/eqv.". Is there any way I can do...
  19. M

    IFERROR Too few arguments

    Excel 2010 My formula: =(AF$8*AF$7)-SUM(AK$5:AK10)-AL10 creates a #VALUE ! error due to AL10 being a blank cell. If I change AL10 to a value it will fill the column with "0.00", and then the next column with zeros also, which I'm trying to avoid simply to present a cleaner spreadsheet. I was...
  20. D

    Format Code for Data Labels to hide zeros when both Value and Percentage are selected as Label Options

    I have a spreadsheet in Excel 2013 where I have a pie graph that displays percentages as the data labels. I had a custom format code, 0.0%;-0.0%; ,to hide zeros from the chart, however a request came through to display both percentages AND values. Now my custom format code is not working for the...

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
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 "".
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