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

Thread: DISPLAY NUMBERS

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    LIZ
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I WOULD LIKE TO ENTER THE NUMBER
    008 AND IT TO DISPLAY AS 008. THIS IS USED
    FOR ENTRY RELATED TO VLOOKUP. PREFER NOT TO FORMAT AS TEXT

    ANY IDEAS?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    CUSTOM FORMAT CELL USING 000?

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You cannot have leading zeroes without formatting as text. BOTH columns must be formatted the same: the column that contains the value you're looking for and the column in which the value should be found.
    ~Anne Troy

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Custom format as 000 will display 008. Checking the cell with =ISNUMBER returns "true", and referencing to a VLOOKUP where the first column contains general format "8" or custom format "008" both return "true", so it should work unless your column in your VLOOKUP range is a text "8"


    :: Pharma Z - Family drugstore ::

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Chris can you you not force excel to have lead zero or zeros and stil add them up (ie they are numbers???? )

    ermm

    If the addition fails or as you test with built in function isnumber thisis not the same... im preplexed!

    as you know Chris my ID works telephone number is a bit odd ill use differn line that when you dial will say no such number.... good eh!

    But i conver our database and this is loaded into the computer for DD dailas and speed dials so the 9 need to be removed and the computer is not set to do this i click 9 and dial to outside so i need 0 for ever call via the computer...

    Preplexed....


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes, I think so, unless I'm missing something

    that was what I was saying - you can custom format them as 000 so they appear as 008 and 007 etc etc... add them up as normal and they add to 15

    checking any of them with =ISNUMBER returns "TRUE" which means they are numbers, not text


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Chris i see post saying yes or no and think eh!

    Thats not right, and question myself, sure we all do from time to time..

    Thats why i post so very few answers, i post what im confidant with and know works as i do ever day, maybe i should post more ..


    ermmm .. im might....

    Like i say chris Excel can do anythink, im sure just knowing how!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jack,

    an interesting point

    I often maintain that Excel *can* do anything, literally. You just need to know how to get it to do it.

    A spreadsheet that displays Michael Schumacher's photo and plays the Italian national anthem depending on what's in the cell....... who'd have thought ?!?!

    You good thing about Excel is it's boolean logic. There is either a right answer or a wrong answer, no inbetweens.

    Take sociology though and deprived inner-city kids : no black and white answers, it's all subjective, opinions and grey areas.

    Not with maths. it either adds up or it doesn't.

    This is where your confidence comes in. Maths doesn't lie to you

    Post more, I suspect a lot of people already consider you an MVP in your area of expertise. I know I do.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris

    warm comments a big thanks and like wise.

    im a simple south london boys, druk up in londons street, simple simplistic veiws, no black no white, you tell me if you see a different face, i do when you point it out ,, to me your simple alright or not no inbetween.

    Mixing with the guys i have readers all over the world reading this will know these guys names, i am proud im a good un trusted and signed pictures hang on my walls and our photo s**** on mt work deak

    ive nothing to prove just try to help... that simple,, ill do what i can.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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
  •