deleting only arabic letters

[osmanoca]

hello dear mrexcel movers.
i have data with kurdish/Turkish and arabic words. so every cell has arabic and kurdish words together. i need to to delete all arabic letters . when i made with kutools it also delete some different letter like ç,ş,ö,î,ê,û.... because it thinks english alphabeth. how can delete only arabic letters.

example:
cümûdiyyeجمودیه
cünbân (F.) [ ]جنبان1
cünbişجنبش
cünd جند
cündî جندی
cünha جنحه
cünûd جنود


thanks for your help.

 

[thisoldman]

Are you OK with a user defined function, VBA?

Public Function TrimArabic(txt) As String
    Dim glyph As String
    Dim i As Long
    
    For i = 1 To Len(txt)
        glyph = Mid(txt, i, 1)
        If WorksheetFunction.Unicode(glyph) < &H600 Then
            TrimArabic = TrimArabic & glyph
        End If
    Next i
End Function

 

[osmanoca]

i need macro, i tried this code, doesnt work. i dont know function.

 

[thisoldman]

A function is similar to a macro sub. A user defined function, a UDF, must go in a vba module, it will not work if it's entered in a Sheet's or Workbook's code. Once the function is entered in a module, you use it just as you would use any built-in Excel function: you enter the formula in a cell. If your mixed text is in cell A1, in B1 enter:

=TrimArabic(A1).

A UDF can be nested just like built-in functions. For instance, TrimArabic doesn't remove any trailing spaces left after cutting the Arabic characters. To remove those spaces, enter

=TRIM(TrimArabic(A1))

Hope this post helps you.

 

[osmanoca]

i pasted that formula (=TrimArabic(A1). in B1 but after clicking enter it shows errror #AD ?

so i couldnt sorry. thanks for help but, if you can give macro will be better.

 

[thisoldman]

OK. I used a worksheet function, =UNICODE(), in my VBA that was introduced with Excel 2013.

This version of the TrimArabic function does not use any workbook functions:

Public Function TrimArabic(txt) As String
''''
' Removes all characters U+0600 and higher from txt.
'
' Designed to remove Arabic characters, it also removes Chinese and
'   many, many other language group characters from txt.
''''
    Dim glyph As String
    Dim i As Long
    
    For i = 1 To Len(txt)
        glyph = Mid(txt, i, 1)
        If glyph < ChrW(&H600) Then
            TrimArabic = TrimArabic & glyph
        End If
    Next i
End Function

The workbook I used for testing can be downloaded from:
https://www.dropbox.com/s/j3ogjh9levm3z8a/trim_arabic_from_text_strings.xlsm?dl=0

 

[osmanoca]

sorry i dont know to use it. i pasted as formula in Column B . but doesnt work. if you can explain me what i will do . how will use that code. it willl be better. or make it as macro. it is easier.. thanks for try.

 

[thisoldman]

Ok. Here it is as a macro. It's designed to remove the characters from a user-selected range.

Option Explicit

Function TrimArabic(txt) As String
''''
' Removes all characters U+0600 and higher from txt.
'
' Designed to remove Arabic characters, it also removes Chinese and
'   many, many other language group characters from txt.
''''
    Dim glyph As String
    Dim i As Long
    
    For i = 1 To Len(txt)
        glyph = Mid(txt, i, 1)
        If glyph < ChrW(&H600) Then
            TrimArabic = TrimArabic & glyph
        End If
    Next i
End Function
''''

Sub RemoveArabic()
''''
' Removes all characters above U+05FF from a selected range.
'
' Requires TrimArabic().
''''
    Dim cell As Range

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each cell In Selection
        cell.Value = TrimArabic(cell.Value)
    Next cell
End Sub
''''

 

[osmanoca]

thanks dear thisoldman , this worked well. thanks much really.

 

[thisoldman]

I'm glad to read that the macro works for you. I appreciate getting a reply when something works correctly.