leading zero

  1. S

    How do I add leading zeroes in this example?

    I want to take the original naming in the image below and turn it into the required naming format. This includes substituting "/" with "-" and then adding a leading zero to sequence number at the end of the number. Using the 1st column, I used a simple Substitute formula to change the "/" to a...
  2. A

    Leading Zero iwith Concat

    I've got two columns, department and position. I want to smush them together into one in order to use vlookup from another sheet. The position number needs to be 3 digits long in the final product. I have 1,2, and 3 digit position numbers. I can of course make it show the leading zeros...
  3. J

    Problems formating numbers with hyphens and leading zeroes.

    Hi, I'm trying to format a list of phone numbers that are using this number format: ###-### ## ## it starts with a zero and it keeps disappearing and when I enter two zeroes to make it appear there is a space that comes out of nowhere making the format look like this: # ##-### ## ## I don't...
  4. 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>
  5. M

    Cannot keep leading zeros in hyperlink address

    This is a code that creates a value in a cell based on the previous line number, it then hyperlinks this cell to the associated file folder for that Inspection number Range "G" is the cell to receive the hyperlink. =SUM(R[-1]C[-6]+1)" Takes the previous line number in from one row above...
  6. S

    copy on double click in sheet1 and then paste into next available row in sheet2.

    I am very new to vba, which i why i am asking this, I'm sure to most people on here this is a very simple process, but I just cant get it to work. So here is what i am trying to do. In sheet one i have a table. in column A, i have an ID number with leading zeros. what i need to happen is when i...
  7. B

    Remove leading zero within a text field

    Hello, I have a list of codes I need to removed the leading zero from. However, I only want to remove the leading zero and not all of them. I have been looking for a while but cant seem to figure it out. Any ideas? The codes look like this: <tbody> AB01 AB02 AB03 AB05 AB10...
  8. S

    Keep leading zeros in concatenation

    I need to convert dollar amounts to 4 digit figures without the decimal and with an "x" before and after. Custom format provides the leading zeros, but they disappear when adding the "x". Can you help? .99 should be x0099x; 1.99 should be x0199x; 21.99 should be x2199x. Thanks!
  9. 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...
  10. S

    Custom Format with leading zeros?

    I have a column of prices which need to be displayed in a four digit format without symbols. It also needs to keep the 4 digit format when used in a concatenate formula. $3.99, $5.00, $.47 should display as 0399, 0500, and 0047 and in the center of the formula as 1501x0399x1523. I either lose...
  11. 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...
  12. J

    Vlookup Issue With Leading Zeros

    I am having an issue with a vlookup today, and wanted to see if I could get some opinions or tips to solve this. I have a list of site codes which contain numbers with two leading zeros. Then on the next tab I have a form created where users type in the site code, and in the adjacent cell the...
  13. S

    Fixed Length, Adding Zeros for an Outline

    Dear All, I am trying to create a formula whereby a number is adjusted to a fixed amount of characters using leading zeros. Wait - it doesn't stop there.. The problem I am encountering is that these numbers are in an outline style - 1.1, 1.1.1, and so on. I need a formula which...
  14. E

    Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

    I have 5 digit and 5+4 = 9 digit zip codes in Excel. Some start with 0 such as 01122. Using the mail merge wizard, the zip codes that begin with zero are getting dropped, which is not what I want. I dont want it to convert a zip from 01122 to 1122, because a zip code should include the...
  15. H

    Add leading zeroes in Function

    Hi, I am trying to format a number to add leading zeroes in a function I have created. The input Integer will have a maximum length of 5 characters, but the output must be five characters with the proper number of leading zeroes. I tried using the format function (ie. Format(Strike, "00000")...
  16. 5

    Losing (UPC Code) Leading Zeros when Converting to CSV

    The Situation: Just before the crash, I output the data from our Point of Sale machine, into a TAB delimited formats, and saved them. Ive now spent over 30 days fighting with this .. I need some help please. Problem #1: I keep losing Leading Zeros in my UPC column. The Solution...
  17. A

    Maintaining Leading Zeros when saved as a .CSV File

    I have a column, formatted as "text" with a 7-character alphanumeric code. Some codes have a leading zero. When I save the Excel file as a .csv, then reopen it, the leading zero disappears. I need that zero as this is my unique identifier field. I've tried the ' in front, I've tried formatting...
  18. M

    Leading Zeros in a .CSV

    Morning everyone. I've got a file that I have to save as a .csv to be run through our integration software. The issue I'm having is that many of our item numbers begin with leading zeros, so when I save the .xls as a .csv, the zeros go away. I've tried to format everything as text, and...
  19. S

    Zip Codes and Leading Zeros

    I have an address database and when I save it as a .csv file the leading zeros in the zip code column disappear. I know that I can format it as text, but when I resave it and reopen it, because its .csv it loses the format. I think I need to change the column from integer to character, but I...
  20. I

    Need to put leading 0s back

    I have a group of #s, some had leading 0s some did not. They should all be 11 digits however even though the column is formatted as text, it took the leading 0s off. I know there is a formula to put them back I just can't figure out what it is... For instance if the length of a2 = 10 add "0"...

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