convert any Arabic numerical to English number

Question

I need to convert the following vb.net code to vba for my ms access database, to be able to convert the Arabic numerical string "٠١٢٣٤٥٦٧٨٩" to an English numerical string "0123456789", i found the code in this url convert Arabic numerical to English

Private Shared Function ArabicToWestern(ByVal input As String) As String
    Dim western As StringBuilder = New StringBuilder

    For Each num As Char In input
        western.Append(Char.GetNumericValue(num))
    Next

    Return western.ToString
End Function

Possible duplicate of [How to call .NET methods from Excel VBA](https://stackoverflow.com/questions/37074533/how-to-call-net-methods-from-excel-vba) -- read this thread thoroughly, it probably contains your best options. — Andre, Nov 30 '17 at 15:50
Can't you just use a simple find and replace for each separate number? — Erik A, Nov 30 '17 at 16:15
yes i need to do it like that , but i dont know how to replace any arabic number to english ?? ( only numbers to be replaced and leave any other arabic characters ) can you help me with that ?? — user1921704, Nov 30 '17 at 16:19
@Andre you don't need to use .net methods to achieve this if these are the only symbols that need to be replaced, and using .Net causes your file to no longer work without adjustment on any machine. Imo this is not a duplicate, since an alternative approach without .net should be used. — Erik A, Nov 30 '17 at 17:31

Erik A · Accepted Answer · 2017-11-30T18:48:23.330

You can use the following function:

Public Function ReplaceArabicNumbers(strInput As String) As String
    Dim numberArray: numberArray = Array("٠", "0", "١", "1", "٢", "2", "٣", "3", "٤", "4", "٥", "5", "٦", "6", "٧", "7", "٨", "8", "٩", "9")
    Dim i As Long
    ReplaceArabicNumbers = strInput
    For i = 0 To 18 Step 2
        ReplaceArabicNumbers = Replace(ReplaceArabicNumbers, numberArray(i), numberArray(i + 1))
    Next i
End Function

This executes a replace for every arabic number, and replaces it to the latin equivalent.

Note that you need to adjust the locale settings to accept arabic symbols in the VBA editor (see this question)

Alternatively, if you don't want to adjust your locale settings:

Public Function ReplaceArabicNumbers(strInput As String) As String
    Dim numberArray: numberArray = Array(ChrW(&H660), "0", ChrW(&H661), "1", ChrW(&H662), "2", ChrW(&H663), "3", ChrW(&H664), "4", ChrW(&H665), "5", ChrW(&H666), "6", ChrW(&H667), "7", ChrW(&H668), "8", ChrW(&H669), "9")
    Dim i As Long
    ReplaceArabicNumbers = strInput
    For i = 0 To 18 Step 2
        ReplaceArabicNumbers = Replace(ReplaceArabicNumbers, numberArray(i), numberArray(i + 1))
    Next i
End Function

Note that this doesn't include the dot, but as specified in the question, only numbers need replacement.

Yes, this worked for me too. I'm using the second function that does not require locale setting adjustment on a MacBook pro running Office 365. — Tony M, Sep 18 '19 at 18:18

score 1 · Answer 2 · answered Nov 30 '17 at 15:38

1

You need to set the cultureInfo for the appendFormat method to US

Private Shared Function ArabicToWestern(ByVal input As String) As String
    Dim western As StringBuilder = New StringBuilder
    Dim ci As CultureInfo = New CultureInfo("en-US", True)
    For Each num As Char In input
        western.AppendFormat(ci, "{0}", Char.GetNumericValue(num))
    Next

    Return western.ToString
End Function

answered Nov 30 '17 at 15:38

Sorceri

7,870
1
29
38

@user1921704 Please see Andre's comment for calling .NET methods. – Sorceri Nov 30 '17 at 16:01

Jonathan Applebaum · Answer 3 · 2017-11-30T18:46:19.197

1

You can build your own Scripting.Dictionary for that task:

Public Function ReplaceArabicNumbers(intpt As String) As String

' Select Tools->References from the Visual Basic menu.
' Check box beside "Microsoft Scripting Runtime" in the list.

    dict.Add Key:=ChrW(&H661), Item:=0
    dict.Add Key:=ChrW(&H662), Item:=1
    dict.Add Key:=ChrW(&H663), Item:=2
    dict.Add Key:=ChrW(&H664), Item:=3

   ' OR ALTERNATIVALY
   ' dict.Add Key:="٠", Item:=0
   ' dict.Add Key:="١", Item:=1
   ' dict.Add Key:="٢", Item:=2
   ' dict.Add Key:="٣", Item:=3

    Dim s As String
    Dim Counter As Integer


    For Counter = 1 To Len(intpt)
        If dict.Exists(Mid(intpt, Counter, 1)) Then
        s = s & dict(Mid(intpt, Counter, 1))
        Else
        s = s & Mid(intpt, Counter, 1)
        End If


    Next Counter

    ReplaceArabicNumbers = s

End Function

edited Nov 30 '17 at 18:46

answered Nov 30 '17 at 17:54

Jonathan Applebaum

5,738
4
33
52

Note that you're dimming `i`, but using `Counter`, and that `Scripting.Dictionary` requires a reference to use. Also, your function throws an error when confronted with strings that contain anything else than an arabic numeral. – Erik A Nov 30 '17 at 18:12
Nice response time. While fixing, the inner critic in me also says that arabic numerals are non-ascii characters, thus you should adjust ascii values to unicode values. – Erik A Nov 30 '17 at 18:19
@ErikvonAsmuth thank you again for pointing out the parts that needed a fix, Access can reference `Scripting.Dictionary` (there is a comment how to reference inside the code from the beginning) – Jonathan Applebaum Nov 30 '17 at 18:38
1

I know that, that's why I said _requires a reference to use_ ;). It's just nice to have that in your answer (or change to late bindings, that's what I usually do) so even novices can use your code. Also, `ChrW(&H661)` is a 1 _(yes, I start with 1 instead of 0 in my answer and yes, I probably shouldn't)_ – Erik A Nov 30 '17 at 18:45

convert any Arabic numerical to English number

3 Answers3