Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    Change variable length of numbers to three character text

    Good Afternoon,

    I have a list of numbers
    1
    05
    01
    26
    427

    and I need to change them all to text like
    001
    005
    001
    026
    427

    Does anyone have a quick way to do this?

    I also have a mix of numbers that are 5 or 6 digits long. I need to add a leading 0 to the 5 digit numbers and change all the numbers to text.

    Any help on these appreciated.

    Meleia

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want to keep them as numbers but display that way, format the cells with a custom number format of 000

    To convert to text you could use a fomula like:
    =TEXT(A1,"000")

    Then you could copy that column and paste values over the originals.

    You can do the similar for the leading zeroes in 5 digit
    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Myers515 (2014-07-16)

  4. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks for answering so quickly. I will need to convert to text so this works. Now that I read your response I may have asked this before a long, long time ago but just couldn't remember how to do it. Brain drain.

    Thanks again.
    Meleia

Posting Permissions

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