Solved: Formatting a string as a number with leading 0s

[Frosty]

I need to convert variable length strings to numbers with commas, however, I need to retain leading zeros, but only if they exist (so neither "#,###" nor "0,000" works for my purposes).

After hunting around, I don't seem to see any code for this, but I thought someone might have an idea, since I know there are additional codes in the number format, but I haven't managed to find an actual list anywhere.

"123456" should become "123,456"
But
"0123456" should become "0,123,456"

Short of manually programming this functionality, does anyone have any ideas?

[Frosty]

As a reference, this is what I'm currently doing... but it seems like it should be easier some how.

Public Function fFormatAndKeepLeadingZeroes(sNumber As String) As String
Dim sFormat As String
Dim i As Integer
Dim x As Integer

'build our custom number format
For i = Len(sNumber) To 1 Step -1
sFormat = "0" & sFormat
x = x + 1
If x Mod 3 = 0 Then
sFormat = "," & sFormat
End If
Next
'remove any preceeding comma, if exists
If Left(sFormat, 1) = "," Then
sFormat = Right(sFormat, Len(sFormat) - 1)
End If
'format original number and return
fFormatAndKeepLeadingZeroes = Format(sNumber, sFormat)
End Function

[macropod]

Hi Frosty,

My approach would be much the same:Public Function Leading0Format(sNumber As String) As String
Dim StrFmt As String, i As Long, j As Long
If IsNumeric(sNumber) Then
For i = Len(sNumber) To 1 Step -1
If IsNumeric(Mid(sNumber, i, 1)) Then
j = j + 1
StrFmt = "0" & StrFmt
If j Mod 3 = 0 Then
If i > 1 Then StrFmt = "," & StrFmt
End If
End If
Next
Leading0Format = Format(sNumber, StrFmt)
End If
End Function

[Frosty]

Sorry for the delay, Paul, but thank you! Much cleaner to choose not to add the comma rather than remove if it exists. Really appreciate a second set of eyes on it.

It does beg the question... is this a missing feature (i.e., "preserve original expression" as a parameter), or a missing number code (some kind of special character between the functionality of # and 0) or is it something which would be so rarely asked as to warrant custom programming?

In any event, this is solved for me, and I very much appreciate the assistance.

- Jason

[macropod]

Hi Jason,

I wouldn't have thought that the use of leading 0s with thousands separators could be described as anything other than a custom format. So, no, I wouldn't consider it a missing feature.