leading zeros

  1. K

    How to Keep Leading Zeros in CSV File Format?

    Hello! I am trying to have a column of numbers include leading zeros so they all contain six digits. Scenario 1. I want all numbers in the first column to have six digits (if they already have 5 digits, add 1 leading zero, 4 digits = 2 leading zeroes, and so on). 2. When I follow a website I...
  2. R

    VBA keep leading zeros

    Sub BO_ID_Prep() Dim rng As Range Dim i As String For Each rng In Selection i = i & rng & ";" Next rng ActiveCell.Offset(1, 1).Value = Trim(i) ActiveCell.Offset(2, 1).Select ActiveCell.FormulaR1C1 = "=LEFT(R[-1]C,LEN(R[-1]C)-1)" Selection.Copy ActiveCell.Offset(1, 0).Select...
  3. R

    sorting problem

    I am having a problem on sorting I have a worksheet with around 36000 rows I am sorting on three columns the first column is mostly Alphabet characters however I have around 800 entries with Numbers My problem is some of these numbers have leading zeros. Also some of these numbers are only...
  4. F

    Concatenating but keeping the leading zeros

    I'm trying to concatenate several columns, one of which contains leading zeros in some of the cells. Example below: Row two is correct (PN # Column) Row 1 is not bringing over the "00" in "003" I have the length column set to display as 003 but concatenate is removing the two 0's. <tbody> PN...
  5. P

    Leading zeros removed after a replace of non-numeric characters leaving only numeric charaters

    I need to remove the forward slashes and leave the results in a data set like the below. I keep losing the leading zeros when I do a replace all with / in the find and left the replace with blank even when the cells are formatted to Text. I am working in Excel 2010 with a regular .xlsx file - I...
  6. 4

    Remove leading 0 before decimal except when another digit precedes the 0.

    I have a column, which has a comma separated values inside each cell that look like this 0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0 1.5, 1.6,2.0, 10.6,10.9, 15.2,30.75 20, 0.25,280.2, 0.29,300.2, 423,530.76 In a text string. The goal is to remove the leading zero<code style="font-family: Consolas...
  7. P

    I need to get large numbers within a string, no leading zeros

    Hi, So I have a list of data that looks somewhat like this ABC00004 ABC01085 ABC00334 ABC00100 I want it to look like this 4 1085 334 100 The ongoing theme is that there will always be 3 letters and then numbers after it. I want to capture the number only, and with no leading zeros, but I...
  8. J

    Custom Number with trailing zeros

    Hi!! I am trying to create a custom number field that will both add four trailing zeros to the end of a number entered into it AND allow the number to begin with a zero. For example I want to type 085464 and have displayed 0854640000. Using custom number 0"0000" adds the four trailing zeros to...
  9. A

    Add two leading zeros to numbers entered into Column(Numbers 18 characters long.)

    Hi All, I need some help and hope you guys can assist. I have a file in work that I populate with 18 digit numbers everyday but I need to add two leading zeros to these before create final docs for customers. I have tried formatting the cells,going to custom and adding them that way but it...
  10. B

    Add leading zeros based on IF text

    I need either a foumula or macro to add leading zeros to a number based on text in another column Column A is the original number Column B is blank and where I want my result to go leaving column A original Column C is where the text is that the leading zeros will be based on A1 = 14M3652100...
  11. K

    Combining 2 cell vaules -- without losing data

    Good afternoon all- I have a data set that I'm trying to sort in order to be efficient for some of my team members. Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive. Any...
  12. S

    Help with imported data from ERP system - issues with leading zeros, text to date

    I just started at a new company and am having issues using the information I have pulled from our ERP system (SyteLine, which uses a SQL back end) into excel. The problem is the formatting of our sku's... there is no common part number format across all of our product lines. Some start...
  13. A

    VLOOKUP between 2 sources of data keeping leading zeros, numbers as text

    Ok, so I have about 15,000 parts that are all different lengths [between two and twenty (or more) places], and different formats i.e.- some with numbers only, some with numbers and letters, some with leading zeros, some with - and + symbols, some with all of the above, like the following list...
  14. L

    HELP! How to Use Concatenate Formula with Leading Zeros??

    Hi All - I hope someone can help me with this! I am using the following formula: =CONCATENATE($Page.$B$6;$'Data'.C9;$Page.$B$7) In the 'Data' range, i have some values with leading zeros. When using the concatenate formula, these leading zeros do not follow through. For example, I get the...
  15. D

    Stop special format 0000 from being changed to a Zip code

    I'm trying to set a cell to accept a 4-digit number that may have leading zeros. I've set Numbers - Special Formatting to "0000" but Excel changes it to be a postal zip code from Portugal. Any idea how to keep it from using an existing formatting when I want to use a custom special formatting?
  16. 5

    UPC Codes w/LEADING ZEROS & NON-Standardized Lengths &/or Templates! (help!)

    <!--[if gte mso 9]><xml> <o:DocumentProperties> <o:Version>12.00</o:Version> </o:DocumentProperties> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/>...
  17. MarkCBB

    Data dump time value challange

    Hi there, I have a database that is downloaded, however some of the time values are not correct: here is a sample: 0 7:37:26 PM 0 9:41:43 PM 0 9:50:13 PM 0 9:40:30 PM 0 9:42:45 PM 0 9:44:45 PM 0 9:45:15 PM 0 9:46:01 PM 0 9:47:01 PM 0 9:47:31 AM 0 9:49:31 PM 12:01:55 AM 04:51:55 PM...
  18. N

    Format Formula Results with Leading Zeros

    I have a lookup formula that is returning values to include in a part number. The value that comes up might be "2103" or "5242698" but I need them all to be formatted with 8 digits so I want the appropriate amnt. of 0s to be added. i.e. Formula Value / Required Format 2103 =...
  19. 1

    Need Leading Zeros

    I have a text column of 7 to 9 digits in length. Some items have 2 leading zeros and some do not. My formula in other areas will not work on the items without the 2 leading zeros. I know how to Custom Format to get them BUT some of the 5 digit items look like this (2BE21, 25E51, etc.). If I...
  20. R

    Excel won't accept leading zero's

    Hi everyone, I am brand spanking new to this forum and a novice excel user. At my work we have Excel 2003 installed. I can use excel to upload my orders to my vendor. I thought I would try that today. I created a sheet which has as headings in the first row: Column 1 named SKU, Column 2 named...

Watch MrExcel Video

This Week's Hot Topics

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