+ Reply to Thread
Results 1 to 7 of 7

How to add one digit "0" in the front of a number

  1. #1
    Registered User
    Join Date
    10-25-2005
    Posts
    13

    How to add one digit "0" in the front of a number

    Hi everyone,

    I have a problem in my excel. I'm trying to add a digit "0" in front of every number in one column. For example: if the number is 1234567, then I want it to become 8 digit number, that is 01234567. So, in conclusion I need one extra digit 0 in front of every number. Is there any way to do that?

    Thank you very much.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,897
    There are a few ways to get what you want. Here are 3:

    If there are always the same number of digits (like in an Account Number with 8 digits):

    Format>Cells>Number tab
    Category: Custom
    Type: 00000000
    (1234567 would display as 01234567)

    or

    Format>Cells>Number tab
    Category: TEXT
    (Since the value would actually be TEXT, you'd have to enter 01234567)


    Alternatively, you could use a formula in another cell:

    B1: =TEXT(A1,"00000000")
    If A1: 1234567 B1 would return the text string "01234567"
    Do any of those help?

    Ron

  3. #3
    Gary''s Student
    Guest

    RE: How to add one digit "0" in the front of a number

    Try:

    ="'0" & A1 and this is not a typo. Its an apostrophe followed by a double
    quote
    --
    Gary''s Student


    "wliong" wrote:

    >
    > Hi everyone,
    >
    > I have a problem in my excel. I'm trying to add a digit "0" in front of
    > every number in one column. For example: if the number is 1234567, then
    > I want it to become 8 digit number, that is 01234567. So, in conclusion
    > I need one extra digit 0 in front of every number. Is there any way to
    > do that?
    >
    > Thank you very much.
    >
    >
    > --
    > wliong
    > ------------------------------------------------------------------------
    > wliong's Profile: http://www.excelforum.com/member.php...o&userid=28343
    > View this thread: http://www.excelforum.com/showthread...hreadid=488198
    >
    >


  4. #4
    Registered User
    Join Date
    10-25-2005
    Posts
    13

    RE: How to add one digit "0" in the front of a number

    Both method works.
    Thanks again Gary.

  5. #5
    Registered User
    Join Date
    10-01-2010
    Location
    Ahmedabad, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to add one digit "0" in the front of a number

    Thanks. I am working on shopping feed & had problem to add 0 digits in UPC code. Now, I have solution with help of 1st suggestion.
    I am working with SEO Company & busy with office chairs.

  6. #6
    Registered User
    Join Date
    09-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    1

    Smile Re: How to add one digit "0" in the front of a number

    Thank you very much.

    Quote Originally Posted by Ron Coderre View Post
    There are a few ways to get what you want. Here are 3:

    If there are always the same number of digits (like in an Account Number with 8 digits):

    Format>Cells>Number tab
    Category: Custom
    Type: 00000000
    (1234567 would display as 01234567)

    or

    Format>Cells>Number tab
    Category: TEXT
    (Since the value would actually be TEXT, you'd have to enter 01234567)


    Alternatively, you could use a formula in another cell:

    B1: =TEXT(A1,"00000000")
    If A1: 1234567 B1 would return the text string "01234567"
    Do any of those help?

    Ron

  7. #7
    Registered User
    Join Date
    04-20-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    77

    Re: How to add one digit "0" in the front of a number

    Hi,

    For new user they can follow the steps as per the below link:-

    http://www.exceltip.com/excel-formul...of-number.html

    Thanks

+ 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