Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Store numbers starting with zero as 2 digit numbers

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Store numbers starting with zero as 2 digit numbers

Format Cells.. Number Custom and then 00
--
Gary's Student


"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 59
Default Store numbers starting with zero as 2 digit numbers

Store the number as text and it will not exclude the beginning zero.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 252
Default Store numbers starting with zero as 2 digit numbers

I. Format as text or precede with an apostrophe
'05
this will display the way you want, but certain formulas, like SUM, will
ignore it. You can still use it in some mathematical operators, like =A1+A2.

II. Use a custom number format of...
00
this will display the way you want, but the value of the cell will be 5. If
you do something like =A1 you will get a result of 5 not 05, unless you
format the new cell with the same custom number format.

"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Store numbers starting with zero as 2 digit numbers

The cell is now displaying the number as 05 but the formula bar shows it is
still 5 so when it is uploaded into another system only the single digit will
carry through

"Gary''s Student" wrote:

Format Cells.. Number Custom and then 00
--
Gary's Student


"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Store numbers starting with zero as 2 digit numbers

Valid point on the custom format. The upload into another system will not
take correctly since the value itself is still 5 instead of 05.

Another technicality, the system it is being uploaded to will need it as a
number format instead of text or it will be ignored.

Any other thoughts?

"Sloth" wrote:

I. Format as text or precede with an apostrophe
'05
this will display the way you want, but certain formulas, like SUM, will
ignore it. You can still use it in some mathematical operators, like =A1+A2.

II. Use a custom number format of...
00
this will display the way you want, but the value of the cell will be 5. If
you do something like =A1 you will get a result of 5 not 05, unless you
format the new cell with the same custom number format.

"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Store numbers starting with zero as 2 digit numbers

The system it is being uploaded to will need it as a two digit
number value instead of text or it will be ignored during the upload.

"Bongard" wrote:

Store the number as text and it will not exclude the beginning zero.


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 620
Default Store numbers starting with zero as 2 digit numbers

=TEXT(a1,"00")

You say that " the system it is being uploaded to will need it as a number
format instead of text or it will be ignored.", but how does the system to
which it is being uploaded know whether it is text or number? Is it being
uploaded directly as an Excel file, or as a text file? If your Excel file
is going directly into the other system, and the other system won't accept
Excel's formatted numbers or text, then you'll probably be better off
changing the other system.
--
David Biddulph

"twisted1825" wrote in message
...
Valid point on the custom format. The upload into another system will not
take correctly since the value itself is still 5 instead of 05.

Another technicality, the system it is being uploaded to will need it as a
number format instead of text or it will be ignored.

Any other thoughts?


"Sloth" wrote:

I. Format as text or precede with an apostrophe
'05
this will display the way you want, but certain formulas, like SUM, will
ignore it. You can still use it in some mathematical operators, like
=A1+A2.

II. Use a custom number format of...
00
this will display the way you want, but the value of the cell will be 5.
If
you do something like =A1 you will get a result of 5 not 05, unless you
format the new cell with the same custom number format.

"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit
number
(05). Any ideas?



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Store numbers starting with zero as 2 digit numbers

I have a direction that could possibly be taken if someone can help me get
there. If the data is a hexadecimal instead of a decimal then it will allow
for a two digit value even if the first number is 0. The only way I've been
able to get there is a dec2hex(n,2) formula. Any ideas how to designate the
cells as hexadecimal without using a formula?

"twisted1825" wrote:

I need to hard code a single digit number (such as 5) as a 2 digit number
(05). Any ideas?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting 8 digit numbers into dates [email protected] Excel Discussion (Misc queries) 11 November 22nd 06 05:30 PM
Copying store numbers and pasting them into a seperate workbook punter Excel Discussion (Misc queries) 2 May 26th 06 11:24 PM
Why are 1/2 my numbers imported as text and the rest as numbers? KBear Excel Discussion (Misc queries) 2 April 21st 06 01:40 PM
count if Jim Excel Worksheet Functions 11 January 21st 06 09:31 PM
how can i move the starting point for the numbers numbers down. Gold9er Setting up and Configuration of Excel 1 November 22nd 05 11:09 PM


All times are GMT +1. The time now is 10:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"