Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Numeric input beginning with 0

  1. #1
    Guest

    Default

    Hello everybody,

    When I have to input a numeric value beginning with 0 in a cell, I have to input using a quote i.e. '01234 As a result, XL remove this quote and show 01234

    When I try to read a .txt file (tab delimited) with a '01234 value, XL show '01234

    I think that XL store he ' as a special value, but which one ?

    Thanks in advance for your help

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Have you tried using a custom format like:

    "0000"



  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you put an apostophe ' in front of anything in a cell Excel interprets that as text. If you type '0123 you'll always see 0123 in the cell. If you type 0123 you might see either 123 or 0123 depending on the format of the cell. If you want to use leading zeros then format the cells as text (Format, Cells, choose Number tab and select Text).

    HTH,
    D

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi dk

    If you format the cell as Text the content will also be Text (no difference than using ').

    The ' is a hidden character that even the Find will not return (which is a pain!).

    As I have said above to keep it numeric just use a custom format of:
    "0000"
    Depends on how many zeros you want of course.



  5. #5
    Guest

    Default

    On 2002-03-12 04:09, Dave Hawley wrote:
    Hi dk

    If you format the cell as Text the content will also be Text (no difference than using ').

    The ' is a hidden character that even the Find will not return (which is a pain!).

    As I have said above to keep it numeric just use a custom format of:
    "0000"
    Depends on how many zeros you want of course.


    I've no problem when I input a value with the keyboard; I just need to do the same with a .txt file. What is the Hex code for this hidden char '

    Thanks

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alt+0+39

    39 Is the Character number.



  7. #7
    Guest

    Default

    I'm OK, Alt+0+39 is the ' character, but if I put this character in a .txt file (before a 0), XL show me '0 and not 0 as when I input thru the keyboard '0

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 05:03, Anonymous wrote:
    I'm OK, Alt+0+39 is the ' character, but if I put this character in a .txt file (before a 0), XL show me '0 and not 0 as when I input thru the keyboard '0
    The apostrophe is treated differently for entered data than it is for imported data. If you want the apostrophe recognized for imported data you can (as a 2nd step) select the imported data and use the Edit | Replace... menu command to replace an apostrophe (') with an apostrophe ('). Otherwise, don't include apostrophes in your tab delimited text file, use the Text to Columns... tool to import your data, and apply the Text format to the column of values with leading zeroes.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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