+ Reply to Thread
Results 1 to 7 of 7

Is this a format issue?

  1. #1
    Forum Contributor
    Join Date
    10-30-2007
    Posts
    125

    Is this a format issue?

    B1 contains 12345
    B2 contains blank
    B3 contains blank

    My formula is =TEXT(B1,"00000"). why does the result in C2 look like 00000 instead of blank when the C column has the same format?

    C1. 12345
    C2. 00000
    C3. blank

    Thank you in advance. confused:
    Last edited by ExcelNewby; 02-22-2009 at 01:45 PM.

  2. #2
    Valued Forum Contributor Rahul Nagar's Avatar
    Join Date
    09-18-2008
    Location
    nasik
    MS-Off Ver
    Excel 2003
    Posts
    465

    Re: Is this a format issue?

    Hello,

    Try this =TEXT(B1,"?????")

    I think this might be helpful
    __________________
    Regards
    Rahul Nagar
    Founder of www.myshortcutkeys.com.


    If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
    Also
    If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

  3. #3
    Forum Contributor
    Join Date
    10-30-2007
    Posts
    125

    Re: Is this a format issue?

    Thank you for your reply. The reason that I need the text to be in "00000" is because if the cell contains only 4 digits (9876) then I would want a zero in front of it (09876).

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Is this a format issue?

    My formula is =TEXT(B1,"00000"). why does the result in C2 look like 00000 instead of blank when the C column has the same format?
    Because the value of the empty cell is taken as zero. You can instead use

    =if(b1="", "", text(b1, "00000"))
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Valued Forum Contributor Rahul Nagar's Avatar
    Join Date
    09-18-2008
    Location
    nasik
    MS-Off Ver
    Excel 2003
    Posts
    465

    Re: Is this a format issue?

    Hello ,

    In that case try this :-

    =IF(B1="", "", TEXT(B1, "00000"))

    i think this might be help you.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Is this a format issue?

    Not sure what you're doing with C that necessitates the TEXT function but thought I would just make the point that you can apply a custom format to B itself of:

    000000

    such that B1 would appear with leading zeroes (012345) whilst B2 & B3 would remain blank.

    As I say not sure as I say if this is useful in this instance or not but thought I would mention nonetheless...

  7. #7
    Forum Contributor
    Join Date
    10-30-2007
    Posts
    125

    Re: Is this a format issue?

    It works great. Thank you All!

+ 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