Formating Number Excel

[Guest_imported]

Hi,
I have a range c1: c150 that contains number like that :
3563; 4568; 4569. What I need is have a function that change
the number to 003563; 00568; 004569. If I write 003563 in to the cell the excel read 3563, I need the excel read it 003563.
Thanks very much.

 

[LittleSmudge]

You seem to have two issues here

1) Convert existing numbers to string eg. convert 4567. to "004567"

2) Enter new numbers in "003452" format

2) Is easy , put an apostrophy ( single quote ) in front of the text and excel with treat is as text

1) Is more diffecult,
In VB you could concatenate the cell like Cell = "'00" & Cell

Of insert a temp column next to the existing one and put
D1 = "'00" & C1 and then copy that down to D150.
Then copy D1150 and 'paste special', 'value' into C1:C150



G LS

 

[asiandoll]

All you need to do is change the format of the cell from numbers to text..

For example: right click on c1 and click on format cells, in the number tab select the TEXT option and click ok.
Once you have done that type 003563. It will keep the format but it will align to the left so you have to change the alignment to the right.

(You may get a comment icon on the top left hand corner just click on the exclamation mark and clcik Ignore error and that should disappear...)

Hope this helps...

 

[ETID]

Highlight the column

select Format>Cells...

select custom and type as many zeros as you need..

I.E. if you want 123 to be displayed as 000123 then type 6 zeros

 

[SkipVought]

Hi, Ester01,

There is a fundamental issue here -- the difference between numbers and numeric characters. And the difference can be clarified by asking this question...

Does this value get operated on mathematically? If the answer is, "Yes," then the leading zeroes are a formatting display issue. Otherwise, the value ought to be handled as a string with a fixed length.

As an example, take the value, 1. The numeric value may typically be strored as an Integer Type in 2 bytes. The hex value would be 0001. On the other hand, the ASCII value of "1" is hex 31, stored in 1 byte and "0001" would be hex 30303031 in 4 bytes.

Excel makes it easy to convert between a string of numeric characters and a numeric value, but as you can see, the values within the system are very different.

Skip,
metzgsk@voughtaircraft.com