Add two leading zeros to numbers entered into Column(Numbers 18 characters long.)

[ace2ace234]

Hi All,

I need some help and hope you guys can assist.

I have a file in work that I populate with 18 digit numbers everyday but I need to add two leading zeros to these before create final docs for customers.

I have tried formatting the cells,going to custom and adding them that way but it does not work once I go over 15 digits.

I also tried text and concentrate formulas but to no avail.

Is there a way/Formula that I can do this?

Any help would be much appreciated.

 

[mole999]

in 2007 i went to a cell typed 1234567891234560, right clicked and format cell, went to custom and entered 18 zeros, which changes the displayed value to
[TABLE="width: 189"]
<colgroup><col width="189"></colgroup><tbody>[TR]
[TD="class: xl63, width: 189, align: right"]001234567891234560[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

in 2007 i went to a cell typed 1234567891234560, right clicked and format cell, went to custom and entered 18 zeros, which changes the displayed value to
[TABLE="width: 189"]
<tbody>[TR]
[TD="class: xl63, width: 189, align: right"]001234567891234560[/TD]
[/TR]
</tbody>[/TABLE]

Try changing that last digit from the zero you have to any other digit... it will be ignored and a 0 will always be placed in its stead.

 

[Rick Rothstein]

Your cells should already be formatted as Text (if not, do so). The following macro will toggle whatever cells you select to between having two leading zeroes and not having two leading zeroes, so you can change the values to display the leading zeroes whenever you want and still have the ability to not have the leading zeroes if desired.

Sub ToggleTwoLeadingZeroes()
  Dim Cell As Range
  For Each Cell In Selection
    If Len(Cell.Value) Then
      Cell.Value = Right("000000000000000000" & Cell.Value, 18 + 2 * (Len(Cell.Value) = 18))
    End If
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ToggleTwoLeadingZeroes) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mole999]

LOL, just thought I had typo'd it

 

[pgc01]

Hi

If you enter 18 digits codes in the cells this means that they are text values.

In this case, if you just want to display 2 leading zeroes you can simply format the cells as:

;;;\0\0@

You are not changing the value of the cell, just how it is displayed.

 

[Rick Rothstein]

Hi

If you enter 18 digits codes in the cells this means that they are text values.

In this case, if you just want to display 2 leading zeroes you can simply format the cells as:

;;;\0\0@

You are not changing the value of the cell, just how it is displayed.

This is where I was unsure of what the OP's ultimate goal was... my assumption was he wanted to maintain some kind of decimal alignment, but that was a pure guess on my part.

 

[pgc01]

This is where I was unsure of what the OP's ultimate goal was... my assumption was he wanted to maintain some kind of decimal alignment, but that was a pure guess on my part.

Likewise. I'm also guessing. I hope one of these approaches to solving the problem will help.

 

[ace2ace234]

Guys thanks for your help I got it to work using PGC01 method of formatting the cells,thanks all for your responses and help once again!!

 

[pgc01]

I'm glad it helped. Thanks for the feedback.