I need to format a digit consisting of 1 to 8 digits, to
a 10 digit format.
In other words;
01 need to become 0100000000
02061 need to become 020610000
etc.
So depending on the number, it needs to be formated to a
10 digit format.
If you're starting with cells *containing* 01 and 02061, then they're text
rather than numbers, so you can't format them to appear as you want. You'll need
to pad them with zeros manually or via macro.
On the other hand, if these values only *appear* as 01 and 02061, but are in
fact the numbers 1 and 2061 formatted with one leading zero, you still can't do
what you want with a single number format because you're putting the zeros on
the wrong side - formatting can't change the magnitude of numbers, so you can
only use formatting to add *leading* zeros, not trailing zeros.
So either way you need to threat these as text. If they're numbers with
differing numbers of leading zeros, you'll need to use a macro to change them to
text with the same number of leading zeros and as many trailing zeros as needed.
If they're text, you'll just need to add the trailing zeros.
If you select the range to be reformatted first, you could use the following
macro.
Sub foo()
Dim c As Range
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If IsNumeric(c.Value) Then c.Value = "'" & Left(c.Text & "0000000000", 10)
Next c
End Sub
This converts the results to text. If you want them to be numbers *with*
possible changes in magnitude, then use this instead.
Sub foo()
Dim c As Range
If Not TypeOf Selection Is Range Then Exit Sub
For Each c In Selection
If IsNumeric(c.Value) Then
c.Formula = Left(c.Text & "0000000000", 10)
c.NumberFormat = "0000000000"
End If
Next c
End Sub
