zero padding in excel 1

[jckrell2]

Hello, can anyone tell me how I can add a zero in front
of a number in excel. I have a column that has numbers such as

5
7
23
16
80

and I need them to be

05
07
023
016
080

Does anyone know how I can do this? Basically I just need to add one zero in front of each number. I'm using excel 97-SR2 if that matters.

Thanks

 

[rdodge]

go into Cells Format (Ctrl-1)

On the "Numbers" tab, click on Custom

Type in the textbox the following line:

"0"@

This will cause the cell to be formatted as Text and insert a 0 in front of any text you type in.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[SkipVought]

jckrell,

What you asked for is NOT padding.

Padding assumes some width or number of characters and adds the pad character before or after the value in order to make the value either in fact or by way of format to appear a constant width.

This is a "0" prefix.

Just a technicality. ;-)

Skip,

http://www.TheOfficeExperts.com

 

[jckrell2]

rDodge,

This works for new entries but I have an existing spreadsheet that has many many existing rows that I need to convert over to the leading 0. Is there a way to convert the existing data?

 

[SkipVought]

Select the cells and run...

for each c in selection
  with c
    .value = .value
  end with
next

Skip,

http://www.TheOfficeExperts.com

 

[rdodge]

Hey Skip, with your code, why bother with the "With" and "End With" statements? You are only changing one property on the range object, dispite the fact you are referring to it 2 times.

Sub EditAllSelectedCells()
Dim C as Range
For Each C in Selection
C.Value = C.Value
Next
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[SkipVought]

Every time an object is evaluated with a child object, property or method, there is overhead in navigating the node "."

You can run a test. I ran one using about 655,000 cells and by using the with structure, it shaves a second off of 44 seconds on my PC.

I'm just in the habit of using that construct and it makes good sense from a design and coding standpoint.

Skip,

http://www.TheOfficeExperts.com

 

[bluedragon2]

Why not just highlite all your numbers and format custom:

0#####

With the number of # as your biggest number. (i.e. If your biggest number is 6 digits, format everything to 0######)

This will put a zero in front of all of your numbers.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

This will also keep your numbers formated as numbers.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[rdodge]

I understand what you are saying Skip as I also use object variables to have reference to the very location that it's stored in memory. However, I only do this if it's at least 2 times, though 2 times is kinda iffy as it probably doesn't really make any sense, but 3 minimal would most definitely make since.

Also notice in my Example, I not only use the object variable, but I also declared my object as specific as I can for 2 programming reasons.

1) If object is not declared, it treated as a Variant which uses up 22 bytes per Variant Variable (arrays are multiple times this by an expoiential amount depending on how many demisions the array has).

2) As you may or may not have picked up from the first reason, the programming language may use the variable in a way that was not intended as such, thus could cause the code to error out, so it's also good practice to declare your variables as specific as possible and on as small of a scope as it is needed as a VB PRoject file can only contain up to something like 64,000 bytes taken up by variables (that's currently in use).

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[SkipVought]

Yea, when I write my routines i always declare each variable. Just don't do it in examples.



Skip,

http://www.TheOfficeExperts.com

 

[jckrell2]

Ok,

I was able to get this done by formatting close to what bluedragon suggested2.

I simply highlighted all the rows in that column and
did the format, number, custom and put 00
The largest number i had was 2 digits so this did put a leading 0 in. Thanks for all the help with this.

 

[KenWright]

'Option Explicit' rules OK!!!!

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[rdodge]

Yeah, that points out any variable that hasn't been explicitly declared. I use it in my programming too.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.