none
How i split english and arabic data into multiple columns RRS feed

  • Question

  • How i split english and arabic data into multiple columns

    i want to separate my english and arabic into different columns please help
    for e.g

    مؤسسة افرينا للتجارة Afrina Trading Est

    مخابز الامير اللبنانية Al Ameer Lebanese Bakeries

    منتجع الامواج Al Amwaj Resort

    مطبخ العرائس Al Arayees Kitchen

    محمصة الاطلال Al Atlal Roastery


    also there is no space and comma between arabic and english 

    Wednesday, April 10, 2013 11:49 AM

Answers

  • Hi.

    Select one columns range and run my VBA code

    Take look on this code:

    Sub ExtractArabicFromEng()
    'MVP OShon VBATools
    Dim x%, el As Range, EngStr$, ArabStr$, r As Range: Set r = Selection
    If r.Column <> 1 Then MsgBox "Select only one column.": Exit Sub
    Const CharList$ = "[A-Za-z0-9]"
    Const Znaki$ = "[ ]" 'or another signs
    Application.ScreenUpdating = False
    For Each el In r
        EngStr = "": ArabStr = ""
        For x = Len(el.Text) To 1 Step -1
            If Mid(el.Text, x, 1) Like CharList Then
                EngStr = Mid(el.Text, x, 1) & EngStr
            ElseIf Mid(el.Text, x, 1) Like Znaki Then
                EngStr = Mid(el.Text, x, 1) & EngStr
                ArabStr = Mid(el.Text, x, 1) & ArabStr
            Else
                ArabStr = Mid(el.Text, x, 1) & ArabStr
            End If
        Next x
        el.Offset(, 1).Value = Trim(ArabStr)
        el.Offset(, 2).Value = Trim(EngStr)
    Next el
    Application.ScreenUpdating = True
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by WaqasF Thursday, April 11, 2013 9:00 AM
    Wednesday, April 10, 2013 1:00 PM

All replies

  • Hi.

    Select one columns range and run my VBA code

    Take look on this code:

    Sub ExtractArabicFromEng()
    'MVP OShon VBATools
    Dim x%, el As Range, EngStr$, ArabStr$, r As Range: Set r = Selection
    If r.Column <> 1 Then MsgBox "Select only one column.": Exit Sub
    Const CharList$ = "[A-Za-z0-9]"
    Const Znaki$ = "[ ]" 'or another signs
    Application.ScreenUpdating = False
    For Each el In r
        EngStr = "": ArabStr = ""
        For x = Len(el.Text) To 1 Step -1
            If Mid(el.Text, x, 1) Like CharList Then
                EngStr = Mid(el.Text, x, 1) & EngStr
            ElseIf Mid(el.Text, x, 1) Like Znaki Then
                EngStr = Mid(el.Text, x, 1) & EngStr
                ArabStr = Mid(el.Text, x, 1) & ArabStr
            Else
                ArabStr = Mid(el.Text, x, 1) & ArabStr
            End If
        Next x
        el.Offset(, 1).Value = Trim(ArabStr)
        el.Offset(, 2).Value = Trim(EngStr)
    Next el
    Application.ScreenUpdating = True
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by WaqasF Thursday, April 11, 2013 9:00 AM
    Wednesday, April 10, 2013 1:00 PM
  • Thanks man you save my life....
    Thursday, April 11, 2013 8:59 AM
  • Thanks to both of you to share the helpful code to us. This will help the others who meet the same issue.

    Jaynet Zhang
    TechNet Community Support

    Thursday, April 11, 2013 9:14 AM
    Moderator