• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Separate Arabic and English text in the same cell

H

Hany ali

Active Member
hello every one .. I want your Help in this problem
I Want to Seperate arabic and English Text as you see in the same Cell To Separate cells or columns ,
So that the Arabic text is in one column and the English text in another separate column.as you see in screen shot

thanks for all
 

Attachments

Last edited:
Hello herofox !​
​
Can you remove the bold in your post as that hurts eyes !​
​
According to your picture you can easily split the text on the second space without any VBA code just with Excel basics formula …​
​
 
thanks alot mr. Marc L ......but I'm Hany ,Not herofox
Yes I found this Formula to extract Arabic Text in Column B .... ARRAY (Ctrl+Shift+Enter)
Code: 
=MID(A2,MATCH(1,IF(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=192,1),0),255)
and for english text in Column C , I Found This Formula

Code: 
=TRIM(LEFT(A2,LEN(A2)-LEN(B2)))
thanks alot mr.Marc ,sorry if possible to Move and Transfer this Post To Ask an Excel Question Dep.
 
Last edited:
If the order of the Latin and Arabic characters is not known the following will work
Code: 
= LET(
  characters, MID(@mixed,SEQUENCE(1,LEN(@mixed)),1),
  selected, IF( (CODE(characters) = 63)+(CODE(characters) = 32), characters, "" ),
  TRIM(CONCAT(selected)) )
provided the latest release of Excel is used. For older versions named formulas for 'characters' and 'selected' will allow the formula to be used without CSE, but the SEQUENCE also needs to revert to ROW/INDIRECT.
 
I realise that the formula will not work on anything but Microsoft 365 beta. I knew you had a solution so there was no need to regress the solution to Office 2016 or such. If, at some time in the future, you need to pick Arabic letters out from a string of Latin ones, it could be combined with elements of your formula.
 
Hany ali said:
Yes I found this Formula to extract Arabic Text in Column B .... ARRAY (Ctrl+Shift+Enter)
=MID(A2,MATCH(1,IF(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=192,1),0),255)
Click to expand...
I think your formula will fail if the Arabic text put in mid of the string.

Here is a formula solution for your reference

In B2, formula copied down :

Code: 
=MID(A2,MATCH(1,0+(MID(A2,MMULT(ROW(INDIRECT("1:"&LEN(A2))),1),1)>="ء"),0),SUM(AGGREGATE({14,15},6,ROW(INDIRECT("1:"&LEN(A2)))/(0+(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)>="ء")=1),1)*{1,-1})+1)

or this shorter,

Code: 
=MID(A2,MATCH(1,0+(MID(A2,MMULT(ROW($1:$99),1),1)>="ء"),0),SUM(AGGREGATE({14,15},6,ROW($1:$99)/(0+(MID(A2,ROW($1:$99),1)>="ء")=1),1)*{1,-1})+1)

Note :

1] I don't have the Arabic Excel version and unable testing of the formula, please post back if it can work or not?

2] In fact English is the 1st language used in the computer by IBM, they started with the code no. range 1-255, after that, all other foreign languages developed their own code and the code no. all greater than 255.

In English Excel version code no. range 1-255, the Code function for all foreign languages return 63. The Arabic code no. range around is 1536-1791

That is the reason why Peter's formula fail in the testing with your Arabic Excel version.

69365
 
Last edited:
thanks
bosco_yip
it's Excellent formula ,worke well
As for the name in English do you have a suggestion for an equation. Or can I use my equation?



Code: 
=TRIM(LEFT(A2,LEN(A2)-LEN(B2)))
 
Hany ali said:
....
As for the name in English do you have a suggestion for an equation. Or can I use my equation?
Code: 
=TRIM(LEFT(A2,LEN(A2)-LEN(B2)))
Click to expand...
The formula in C2 for English text, I suggest to use :

=SUBSTITUTE(A2," "&B2,"")

Because the above formula used 1 function, your formula used 4 functions

Regards
 
Hi @bosco_yip
There are so many reasons why my formula may not work on the the OP Excel version; the main one being that, left to my own inclinations, I only develop for Microsoft 365 these days. The point you raise about the localisation is interesting though; I had rather assumed that the return code of 63 (Latin "?") for unknown characters was a property of the CODE function, rather than the localisation, but I have no way of testing the idea. I probably should have used UNICODE but that too is only available in Office 2019 and 365.
 
Here's a shorter formula for extract the Arabic text in a mix text string

In B2, copied down :

Code: 
=MID(A2,MATCH(1,0+(MID(A2,MMULT(ROW($1:$99),1),1)>="ء"),0),SUMPRODUCT(0+(MID(A2,ROW($1:$99),1)>="ء"))+1)

69384
 
bosco_yip said:
The formula in C2 for English text, I suggest to use :

=SUBSTITUTE(A2," "&B2,"")

Because the above formula used 1 function, your formula used 4 functions

Regards
Click to expand...

Hi

I had used this formula for getting English Text but getting complete text instead of only English Text

Thank you.
 
You must log in or register to reply here.
Back
Top