Getting Number in 12,34,56,789 this format

Chirag · Joined: 04 Oct 2007 Posts: 13

I want to get Numbers

in

12,34,56,789

As it is needed that the commas should be in such a position and not in a
millions Style

I tried Format() But do not know Which User Defined Format to give

Waiting for Help

Thanks in Advance

Archived from group: microsoft>public>vb>enterprise

Al Reid · Joined: 04 Oct 2007 Posts: 5

Here is an example that works for the specific case you provided.

Format$("12345678","##,##,##,###")

I am sure you can adapt it for the general case Wink

Al Reid

"Chirag" wrote in message@TK2MSFTNGP10.phx.gbl...
> I want to get Numbers
>
> in
>
> 12,34,56,789
>
> As it is needed that the commas should be in such a position and not in a
> millions Style
>
> I tried Format() But do not know Which User Defined Format to give
>
> Waiting for Help
>
> Thanks in Advance
>
>

Ken Halter · Joined: 04 Oct 2007 Posts: 4150

This seems to do the trick....
'================
Option Explicit

Private Sub Command1_Click()
Debug.Print CustomFormat(123456789)
Debug.Print CustomFormat(23456789)
Debug.Print CustomFormat(3456789)
Debug.Print CustomFormat(456789)
Debug.Print CustomFormat(56789)
Debug.Print CustomFormat(6789)
Debug.Print CustomFormat(789)
Debug.Print CustomFormat(89)
Debug.Print CustomFormat(9)
End Sub

Private Function CustomFormat(NumIn As Long) As String
Dim sStr As String
Dim sFuncReturn As String
Dim iSection As Integer
Dim iNewLen As Integer
Dim sSeparator As String

sStr = "" & NumIn
iSection = 3
Do
sFuncReturn = Right$(sStr, iSection) & sSeparator & sFuncReturn
iNewLen = Len(sStr) - iSection
If iNewLen > 0 Then
sStr = Left$(sStr, Len(sStr) - iSection)
iSection = 2
sSeparator = ","
Else
Exit Do
End If
Loop While Len(sStr) > 0

CustomFormat = sFuncReturn
End Function
'================

--
Ken Halter - MS-MVP-VB - http://www.vbsight.com
Please keep it in the groups..

"Chirag" wrote in message@TK2MSFTNGP10.phx.gbl...
> I want to get Numbers
>
> in
>
> 12,34,56,789
>
> As it is needed that the commas should be in such a position and not in a
> millions Style
>
> I tried Format() But do not know Which User Defined Format to give
>
> Waiting for Help
>
> Thanks in Advance
>
>

Ken Halter · Joined: 04 Oct 2007 Posts: 4150

Actually, that displays the normal formatting. 12,345,678

--
Ken Halter - MS-MVP-VB - http://www.vbsight.com
Please keep it in the groups..

"Al Reid" wrote in message%23tHfDHA.2984@TK2MSFTNGP11.phx.gbl...
> Here is an example that works for the specific case you provided.
>
> Format$("12345678","##,##,##,###")
>
> I am sure you can adapt it for the general case Wink

>
> Al Reid

Al Reid · Joined: 04 Oct 2007 Posts: 5

oops!! I tried it in the immediate window with my eyes closed. Looking
back at it, it's plain as day.

Al

"Ken Halter" wrote in message@TK2MSFTNGP12.phx.gbl...
> Actually, that displays the normal formatting. 12,345,678
>
> --
> Ken Halter - MS-MVP-VB - http://www.vbsight.com
> Please keep it in the groups..
>
>
> "Al Reid" wrote in message
> %23tHfDHA.2984@TK2MSFTNGP11.phx.gbl...
> > Here is an example that works for the specific case you provided.
> >
> > Format$("12345678","##,##,##,###")
> >
> > I am sure you can adapt it for the general case Wink

> >
> > Al Reid
>
>

Al Reid · Joined: 04 Oct 2007 Posts: 5

Ken,

Yours is better but, I think this one at least works:

Public Function CustomFormat(ByVal inNumber As Long) As String

Dim intPos As Integer
Dim strNumber As String
Dim intNumLen As Integer

strNumber = CStr(inNumber)
intNumLen = Len(strNumber)
For intPos = intNumLen To 1 Step -1
If (intNumLen - intPos > 2) And ((intPos - 1 - (intNumLen Mod 2)) Mod
2 = 0) Then
strNumber = Replace(strNumber, Mid$(CStr(inNumber), intPos, 1),
Mid$(CStr(inNumber), intPos, 1) & ",", , 1)
End If
Next

CustomFormat = strNumber

End Function

Al

"Ken Halter" wrote in message@TK2MSFTNGP12.phx.gbl...
> Actually, that displays the normal formatting. 12,345,678
>
> --
> Ken Halter - MS-MVP-VB - http://www.vbsight.com
> Please keep it in the groups..
>
>
> "Al Reid" wrote in message
> %23tHfDHA.2984@TK2MSFTNGP11.phx.gbl...
> > Here is an example that works for the specific case you provided.
> >
> > Format$("12345678","##,##,##,###")
> >
> > I am sure you can adapt it for the general case Wink

> >
> > Al Reid
>
>

Albert Reid · Joined: 04 Oct 2007 Posts: 12

at least sometimes...

Al Reid

> Ken,
>
> Yours is better but, I think this one at least works:
>
> Public Function CustomFormat(ByVal inNumber As Long) As String
>
> Dim intPos As Integer
> Dim strNumber As String
> Dim intNumLen As Integer
>
> strNumber = CStr(inNumber)
> intNumLen = Len(strNumber)
> For intPos = intNumLen To 1 Step -1
> If (intNumLen - intPos > 2) And ((intPos - 1 - (intNumLen Mod 2))
Mod
> 2 = 0) Then
> strNumber = Replace(strNumber, Mid$(CStr(inNumber), intPos, 1),
> Mid$(CStr(inNumber), intPos, 1) & ",", , 1)
> End If
> Next
>
> CustomFormat = strNumber
>
> End Function
>
> Al
>
> "Ken Halter" wrote in message
> @TK2MSFTNGP12.phx.gbl...
> > Actually, that displays the normal formatting. 12,345,678
> >
> > --
> > Ken Halter - MS-MVP-VB - http://www.vbsight.com
> > Please keep it in the groups..
> >
> >
> > "Al Reid" wrote in message
> > %23tHfDHA.2984@TK2MSFTNGP11.phx.gbl...
> > > Here is an example that works for the specific case you provided.
> > >
> > > Format$("12345678","##,##,##,###")
> > >
> > > I am sure you can adapt it for the general case Wink

> > >
> > > Al Reid
> >
> >
>
>