Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: numbers to text and insert leading zeros

  1. #1
    Join Date
    7th June 2005
    Posts
    9

    numbers to text and insert leading zeros

    what is the best way to convert numbers to text and insert leading zeros at the same time? Columns of numbers to insert leading zeros and change to text:
    654 00000654
    65465 00065465
    654655 00654655
    54 00000054
    1900548 01900548
    2245659 02245659

    I want them all to end up 8 digits, padding the front with zeros as needed.
    I tried data, text to columns, but how can I insert the zeros? (large files)

    Thank you,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    Re: numbers to text and insert leading zeros

    Can you not just goto Format>Cells..., pick Custom Format and enter 00000000?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th June 2005
    Posts
    9

    Re: numbers to text and insert leading zeros

    it changes the displayed values, but when I load the file to another program, it removes the zeros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th March 2004
    Posts
    561

    Re: numbers to text and insert leading zeros

    try the following if number is in A1 in B1

    VB:
    =text(a1,"00000000") 
    
    
    Change a1 to appropriate cell referance. Copy down appropriate range.

    The select the column and do a copy/paste special value

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th June 2005
    Posts
    9

    Re: numbers to text and insert leading zeros

    Thanks very much.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th March 2004
    Posts
    561

    Re: numbers to text and insert leading zeros

    You're welcome

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Add Missing Leading Zeros To Text
    By Rangerst in forum EXCEL HELP
    Replies: 11
    Last Post: August 7th, 2008, 21:59
  2. Join Text With Number With Leading Zeros
    By dh273 in forum EXCEL HELP
    Replies: 4
    Last Post: July 25th, 2007, 18:36
  3. Pad Numbers With Leading Zeros
    By khenzel in forum EXCEL HELP
    Replies: 4
    Last Post: July 19th, 2007, 11:03
  4. Retain leading zeros in numbers
    By chshiba in forum EXCEL HELP
    Replies: 3
    Last Post: August 4th, 2006, 16:29

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
  •  
porno