By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

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

P: 1
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?
Apr 23 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. strID = Right("0000" & CStr(rs!customerID), 5)
  2. 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?
Apr 24 '07 #2

Robbie
100+
P: 180
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function NumberSeparate(NumberToSeparate, Optional SeparatingChar As String = " ") As String
  2. '
  3. 'NumberToSeparate - The original number
  4. 'SeparatingChar -   What to separate the digits with.
  5. '                   Usually a comma, space, or dot.
  6. '                   By default it is a space.
  7. '
  8. 'Gives back a string with the number nicely separated at every
  9. 'third digit.
  10.  
  11.  
  12. Dim CurrentDigit As Byte
  13. Dim CurrentBackward As Integer
  14.  
  15. CurrentDigit = 0
  16. NumberSeparate = Str(NumberToSeparate)
  17.  
  18.     For CurrentBackward = Len(NumberSeparate) To 1 Step -1
  19.  
  20.         CurrentDigit = CurrentDigit + 1
  21.         If CurrentDigit = 3 Then
  22.  
  23.             NumberSeparate = Mid(NumberSeparate, 1, CurrentBackward - 1) + SeparatingChar + Mid(NumberSeparate, CurrentBackward, Len(NumberSeparate) - (CurrentBackward - 1))
  24.             CurrentDigit = 0
  25.  
  26.         End If
  27.  
  28.     Next CurrentBackward
  29.  
  30.     'Remove any accidental separating chars on the left
  31.     'before any digits!
  32.     While Mid(NumberSeparate, 1, 1) = SeparatingChar
  33.         NumberSeparate = Mid(NumberSeparate, 2, Len(NumberSeparate) - 1)
  34.     Wend
  35.  
  36.  
  37. End Function
  38.  
Apr 24 '07 #3

Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. Public Function NumberSeparate(NumberToSeparate, Optional SeparatingChar As String = " ") As String
  2.   ' NumberToSeparate - The original number
  3.   ' SeparatingChar -   What to separate the digits with.
  4.   '                    Usually a comma, space, or dot.
  5.   '                    By default it is a space.
  6.   ' Gives back a string with the number nicely separated at every
  7.   ' third digit.
  8.  
  9.   NumberSeparate = Replace(Format(NumberToPad, "#,###"), ",", SeparatingChar)
  10.  
  11. 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.
Apr 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.