DISPLAY NUMBERS

HAWK · Apr 5th, 2002, 09:50 PM

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 · Apr 5th, 2002, 09:53 PM

CUSTOM FORMAT CELL USING 000?

Anne Troy · Apr 5th, 2002, 10:05 PM

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 · Apr 6th, 2002, 05:11 AM

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 · Apr 6th, 2002, 09:03 AM

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 · Apr 6th, 2002, 10:47 AM

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 · Apr 6th, 2002, 12:11 PM

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 · Apr 6th, 2002, 01:06 PM

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 · Apr 6th, 2002, 02:10 PM

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.

Apr 5th, 2002, 10:05 PM	#3
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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

Apr 6th, 2002, 05:11 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ::

Apr 6th, 2002, 09:03 AM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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"

Apr 6th, 2002, 12:11 PM	#7
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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"

Apr 6th, 2002, 02:10 PM	#9
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	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. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Apr 5th, 2002, 09:50 PM	#1
HAWK New Member Join Date: Mar 2002 Location: LIZ Posts: 19	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?

Apr 5th, 2002, 09:53 PM	#2
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	CUSTOM FORMAT CELL USING 000?

Apr 6th, 2002, 10:47 AM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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

Apr 6th, 2002, 01:06 PM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode