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
VBA: Insert Multiple Mark (.) Between Number
-
- 3StarLounger
- Posts: 240
- Joined: 17 Feb 2022, 05:16
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Insert Multiple Mark (.) Between Number
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
Hans
-
- 3StarLounger
- Posts: 240
- Joined: 17 Feb 2022, 05:16
Re: VBA: Insert Multiple Mark (.) Between Number
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Insert Multiple Mark (.) Between Number
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
Hans
-
- 3StarLounger
- Posts: 240
- Joined: 17 Feb 2022, 05:16
Re: VBA: Insert Multiple Mark (.) Between Number
wow..yes...thank you hans..
worked like my expected.
worked like my expected.
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: VBA: Insert Multiple Mark (.) Between Number
Or
or even the following, which will work on a selection of multiple cells, even non-contiguous:
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
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
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: VBA: Insert Multiple Mark (.) Between Number
(note the above assumes you are using a language setting in which ',' is the thousands seperator)
-
- 4StarLounger
- Posts: 563
- Joined: 27 Jun 2021, 10:46
Re: VBA: Insert Multiple Mark (.) Between Number
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:
or for a selection of multiple cells,
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