Did not see any reply from yesterday. Need data formatting help

T

TechGuyatwork

Ok I have data in excel that looks like this:

2345
23667867
12345678
8564
3557
The data needs to be formatted to look like this
00002345
23667867
12345678
00008564
00003557
How do I add the zero to eacfh of the fields?
Does this entail scripting of some sort?
Please reply!!! ASAP!!!
 
R

Ron de Bruin

Hi TechGuyatwork

Format the cells like this

Select them
Ctrl-1
Custom, use 00000000
 
R

Ron Coderre

Try this:

Format>Cells>Number
Category: Custon
Type: 00000000
Click [OK]

Note: that won't actuall prepend zeros to the the numbers. It will just
display zeros where needed.

Does that help?
 
A

Alex

Hello

Here is a quick fix with a caveat...

Suppose data start in cell A1 and goes down column 1...type in cell B1...

=IF(LEN(A1)<8,"0000"&A1,A1)

And then drag this formula down.

Caveats...

(1)In your example I assume you either have data of 4 digits or 8 digits in
length.

In the formula, the first part assesses whether the data is less then 8
digits. If so it adds the 0000, otherwise the data remains as it is.

(2) By adding "0000" you are adding a string to the data and so the values
produced are no longer of number format. If you try to type 00001234 in Excel
then you just get 1234 as a number format. To get around this I have made the
data into a string format. You will notice this as those values will be
aligned leff in the cell but others that have not had 0000 added will be
aligned right. The result of this is that it may affect any calculations you
may want to do using the data. Hence this is a quick and dirty fix...for
aesthetics only...

Alex
 
Top