DISPLAY NUMBERS

[HAWK]

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?

 

[jbyrne]

CUSTOM FORMAT CELL USING 000?

 

[Anne Troy]

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.

 

[Chris Davison]

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"

 

[Jack in the UK]

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....

 

[Chris Davison]

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

 

[Jack in the UK]

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!

 

[Chris Davison]

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.

 

[Jack in the UK]

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 stits on mt work deak

ive nothing to prove just try to help... that simple,, ill do what i can.