+ Reply to Thread
Results 1 to 10 of 10

dropping the leading zero

  1. #1
    Registered User
    Join Date
    05-11-2012
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2010
    Posts
    20

    dropping the leading zero

    I am having issues with leading zeros dropping off when I copy/paste into cells. I do Vlookup and convert my columns to text to be sure that I will get a return on my lookup. The numbers I use are manufacturer or vendor part numbers such as 02273480. In some cases, when I add this number into a cell, the leading zero is gone and I will not get a match. I know that if I add ' to the cell first, the zero stays but when I am pasting 70-100,000 lines at a time, that isnt an option. any help would be great.


    Thanks

    Kelly

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: dropping the leading zero

    Hi Kelly,

    Try using this formula... =TEXT(A1,"00000000")

    The number of zeros inside the quote will depend how many you need...

    Hope this helps...

    - Dennis

    re-post: are you comparing numbers to numbers? If so, the format won't matter... but if you are comparing number and text will be a problem...

    Why don't you attach a sample workbook for us to look at...
    Last edited by djapigo; 11-08-2012 at 01:52 PM.

  3. #3
    Registered User
    Join Date
    05-11-2012
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: dropping the leading zero

    If you do the "Find/replace" and remove the periods and replace with nothing, the leading zero drops.

    test excel.xlsx

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: dropping the leading zero

    If you add you're desired result in the sheet, you get an better anwer.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    05-11-2012
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: dropping the leading zero

    I need the leading zero to stay in place when I remove the periods or dashes.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: dropping the leading zero

    Like the solution in #2.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-11-2012
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: dropping the leading zero

    sorry, the first upload was the test sheet I used and the zeros were already removed. here is the sheet I want to remove the periods and keep the leading zeros.

    22test excel.xlsx

  8. #8
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: dropping the leading zero

    Hi Kelly,

    Try this formula... =TEXT(SUBSTITUTE(A1,".",""),"000000000000")

    re-post: hmmm, what to do with row 14? do you only want 1 leading zero?

    re-post2: just use this... =SUBSTITUTE(A1,".","")
    Last edited by djapigo; 11-08-2012 at 02:51 PM.

  9. #9
    Registered User
    Join Date
    05-11-2012
    Location
    St. Louis MO
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: dropping the leading zero

    I need the numbers to remain as they are but without the periods and dashs. I hope that makes sense

  10. #10
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: dropping the leading zero

    Do a 2 step process, use the the SUBSTITUTE formula first with the "." then with "-"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1