VBA: Insert Multiple Mark (.) Between Number

Susanto3311
3StarLounger
Posts: 240
Joined: 17 Feb 2022, 05:16

VBA: Insert Multiple Mark (.) Between Number

Post by Susanto3311 »

hi all...

how to insert mark dot (.) into number from right position, start n number 3 from right
i want this step:
1. select number;
2. run macro.

here my combination sample
typed Rp888888888888888 --- after run macro Rp888.888.888.888.888
etc...

this below all combination
888.888.888.888.888
88.888.888.888.888
8.888.888.888.888
888.888.888.888
88.888.888.888
8.888.888.888
888.888.888
88.888.888
8.888.888
888.888
88.888
8.888
888

thanks for your helping
susant

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA: Insert Multiple Mark (.) Between Number

Post by HansV »

Try this:

Code: Select all

Sub InsertPeriods()
    Dim s As String
    Dim t As String
    Dim i As Long
    Dim j As Long
    s = Selection.Text
    For i = Len(s) To 1 Step -1
        t = Mid(s, i, 1) & t
        j = j + 1
        If i > 1 Then
            If IsNumeric(Mid(s, i - 1, 1)) Then
                If j Mod 3 = 0 Then
                    t = "." & t
                End If
            End If
        End If
    Next i
    Selection = t
End Sub
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 240
Joined: 17 Feb 2022, 05:16

Re: VBA: Insert Multiple Mark (.) Between Number

Post by Susanto3311 »

hi hans...thank you
but not fully work
after run code
Rp99.999.999 – this corect
Rp100.00 – false – shoud be Rp10.000
Rp10.000.00 – false shoud be Rp1.000.000

after 3 digits from right

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA: Insert Multiple Mark (.) Between Number

Post by HansV »

Is this better?

Code: Select all

Sub InsertPeriods()
    Dim s As String
    Dim t As String
    Dim i As Long
    Dim j As Long
    Do While Right(Selection, 1) = vbCr Or Right(Selection, 1) = " "
        Selection.MoveEnd Count:=-1
    Loop
    s = Selection.Text
    For i = Len(s) To 1 Step -1
        t = Mid(s, i, 1) & t
        j = j + 1
        If i > 1 Then
            If IsNumeric(Mid(s, i - 1, 1)) Then
                If j Mod 3 = 0 Then
                    t = "." & t
                End If
            End If
        End If
    Next i
    Selection = t
End Sub
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 240
Joined: 17 Feb 2022, 05:16

Re: VBA: Insert Multiple Mark (.) Between Number

Post by Susanto3311 »

wow..yes...thank you hans..
worked like my expected.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA: Insert Multiple Mark (.) Between Number

Post by SpeakEasy »

Or

Code: Select all

Public Sub InsertPeriods2()
    Dim strSource As String  
    strSource = Selection
    Selection = Left(strSource, 2) & Replace(Format(Right(strSource, Len(strSource) - 2), "#,###"), ",", ".")
End Sub
or even the following, which will work on a selection of multiple cells, even non-contiguous:

Code: Select all

Public Sub InsertPeriods3()
    Dim strSource As Range    
    For Each strSource In Selection.Cells
        strSource = Left(strSource, 2) & Replace(Format(Right(strSource, Len(strSource) - 2), "#,###"), ",", ".")
    Next
End Sub

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA: Insert Multiple Mark (.) Between Number

Post by SpeakEasy »

(note the above assumes you are using a language setting in which ',' is the thousands seperator)

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: VBA: Insert Multiple Mark (.) Between Number

Post by SpeakEasy »

Ok, so I note from other posts that you seem to be based in Indonesia, so assuming that your regional settings are set to Indonesia, the following should also work for a single selected cell:

Code: Select all

Public Sub InsertPeriods4()
    Dim strSource As String
    strSource = Selection
    strSource = Format(strSource, "currency")
    Selection = Left(strSource, Len(strSource) - 3)
End Sub

or for a selection of multiple cells,

Code: Select all

Public Sub InsertPeriods5()
    Dim strSource As Range
    For Each strSource In Selection.Cells
        strSource = Format(strSource, "currency")
        strSource = Left(strSource, Len(strSource) - 3)
    Next
End Sub