Turning customerID-number "10205" into layout "10 205"

[Nevyn]

How do I do that? Turning the layout from the customerID-number "10205" into looking like "10 205"? I've just incresed it from four digits to five, and it looks like this: lsvItem.SubItems(1) = Right("0000" & CStr(rs!customerID), 5), but how do I affect the layout?

[Killer42]

How do I do that? Turning the layout from the customerID-number "10205" into looking like "10 205"? I've just incresed it from four digits to five, and it looks like this: lsvItem.SubItems(1) = Right("0000" & CStr(rs!customerID), 5), but how do I affect the layout?

One fairly simple way would be...

strID = Right("0000" & CStr(rs!customerID), 5)
lsvItem.SubItems(1) = Left(strID,2) & " " & mid(strID,3)

You could probably do it more simply using the Format() function, I'm just too lazy to look it up right now.

The precise method would depend a bit on the format of the customerID field - is it text or numeric?

[Robbie]

I think you mean you want to put a space after every third digit starting from the right to separate hundreds, thousands, millions, hundred-millions etc...?
Example: 99999999 -> 99 999 999
If so, I'll try to make a function for you...

Here we are! :D Tested and working:

Public Function NumberSeparate(NumberToSeparate, Optional SeparatingChar As String = " ") As String
'
'NumberToSeparate - The original number
'SeparatingChar -   What to separate the digits with.
'                   Usually a comma, space, or dot.
'                   By default it is a space.
'
'Gives back a string with the number nicely separated at every
'third digit.
 
 
Dim CurrentDigit As Byte
Dim CurrentBackward As Integer
 
CurrentDigit = 0
NumberSeparate = Str(NumberToSeparate)
 
    For CurrentBackward = Len(NumberSeparate) To 1 Step -1
 
        CurrentDigit = CurrentDigit + 1
        If CurrentDigit = 3 Then
 
            NumberSeparate = Mid(NumberSeparate, 1, CurrentBackward - 1) + SeparatingChar + Mid(NumberSeparate, CurrentBackward, Len(NumberSeparate) - (CurrentBackward - 1))
            CurrentDigit = 0
 
        End If
 
    Next CurrentBackward
 
    'Remove any accidental separating chars on the left
    'before any digits!
    While Mid(NumberSeparate, 1, 1) = SeparatingChar
        NumberSeparate = Mid(NumberSeparate, 2, Len(NumberSeparate) - 1)
    Wend
 
 
End Function
 

[Killer42]

Thanks for that.

I don't mean to rain on your parade, but it seems to me you could achieve much the same end with one statement...

Public Function NumberSeparate(NumberToSeparate, Optional SeparatingChar As String = " ") As String
  ' NumberToSeparate - The original number
  ' SeparatingChar -   What to separate the digits with.
  '                    Usually a comma, space, or dot.
  '                    By default it is a space.
  ' Gives back a string with the number nicely separated at every
  ' third digit.
 
  NumberSeparate = Replace(Format(NumberToPad, "#,###"), ",", SeparatingChar)
 
End Function

(Sorry, but since I recently discovered the Split and Replace functions I've been having great fun finding uses for them.)

Oh, and this might need adjustment to handle different regional number-formatting settings.