clean up arabic

[mohadin]

hI;
I have in the attaced file a combination of possiblelites
I need a regexp macro to clean up spaces and /
Is it possible?
please help

 

[Hui]

You haven't attached anything ?

 

[Marc L]

Hi !

I need a regexp macro to clean up spaces and /
Is it possible?

Yet possible with worksheet formula functions like TRIM, CLEAN and MID !

By general VBA using Replace function …
​

 

[mohadin]

oops
sorry for that
here oyu are

 

[mohadin]

h

Hi !

Yet possible with worksheet formula functions like TRIM, CLEAN and MID !

By general VBA using Replace function …
​

you are correct sir, I have this
=IFERROR(TRIM(MID(B2;1;IFERROR(FIND("/";B2;1)-1;LEN(B2))))&" "&TRIM(MID(C2;1;IFERROR(FIND("/";C2;1)-1;LEN(C2))));"")
but I need a vba for 10000 line

 

[Hui]

do

=CLEAN(SUBSTITUTE(A1,"/",""))
or
=TRIM(SUBSTITUTE(A1,"/",""))

work?

 

[mohadin]

well,
its ok but if you noticed any thing afterr "/" should be cleand up as well
thank you

 

[mohadin]

No other way?

 

[YasserKhalil]

Try this code

Sub Test()
    Dim arr, i As Long, x
   
    arr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = 1 To UBound(arr, 1)
        arr(i, 1) = Trim(Split(arr(i, 1), "/")(0))
    Next i
    Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(, 1).Value = arr
End Sub

 

[mohadin]

Hi;
Thank you Mr. Yaser
It's working perfctly, apprecated
Well since I'm trying get understanding what Mr. Jindon method ( Regex) That why I asked for in my first Thread.
Thank you again
best regards

 

[jindon]

Try

Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Replace " ", "", 2
        .Value = Evaluate("if(" & .Address & "<>"""",iferror(replace(" & .Address & "&""/"",find(""/""," & _
            .Address & "),len(" & .Address & "),"""")," & .Address & "),"""")")
    End With
End Sub

 

[mohadin]

hi Mr. Jindon
It it perfect with one issu
it delet the space in the name with two silables
for examlpe the in ( محي الدين orعلاء الدين) after run the macro it become ( محيالدين -علاء الدين)
is it possible to overcom this issu
thank you so much sir

 

[jindon]

I thought you wanted to delete the space(s)...

Sub test()
   With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(" & .Address & "<>"""",iferror(replace(" & .Address & "&""/"",find(""/""," & _
            .Address & "),len(" & .Address & "),"""")," & .Address & "),"""")")
   EndWith
EndSub

Or just

Sub test()
    Columns("a").Replace "/*", "", 2
End Sub

 

[mohadin]

Mr. jindon
Unbelivable.
You are great
Its magic
thak you thankyou thank you
Terra like
by the way while waitingfor your magic solution I did this to your first code

Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Replace " ", "", 1
        .Value = Evaluate("if(" & .Address & "<>"""",iferror(trim(replace(" & .Address & "&""/"",find(""/""," & _
            .Address & "),len(" & .Address & "),"""")),trim(" & .Address & ")),"""")")
    End With
End Sub

???

 

[jindon]

Try delete one line

.Replace " ", "", 1

 

[mohadin]

Mr. jindon
Genius
thank you

 

[mohadin]

Solved