Align two word in the same cell in different way

[YasserKhalil]

Hello everybody
I have data in Column H in range("H2:H" & LastRow)
Each cell has two words ...one in Arabic and the other in English
I need a macot that enables me to aligh the first word in Arabic to the right and the second word in English to the left
Thanks advanced for help

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much Mr. Karedog for this wonderful code
The results are good but the results are not the expected ..
I need to align the Arabic word to the right in the first line and the English word to be aligned to the left the second line
Thanks a lot

[karedog]

So the Arabic part is reversed ? Please try this modified code :

Please Login or Register  to view this content.

[Richard Buttrey]

..or a formula solution - which could also be added by a macro if that's vital. Assumes a total length of 30 characters. Adjust as necessary

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot for helping me in this issue
I have attached the desired results

[Richard Buttrey]

Clearly we have a different interpretation of the words align right & left. In Excel terminology 'aligning' is associated with the positioning of values to left edge, middle or right edge of a cell.

It seems your requirement is to align in the centre with three characters separating the two words. It always pays to be precise or we waste our time.

[YasserKhalil]

I'm sorry Mr. Richard for not being precise
I meant to make the Arabic word to the right in the first line and the English word to the left in the second line (I didn't mean align literally) .. and try to forgive me as English in not my native language
Best Regards

[Richard Buttrey]

...indeed and understood.
It was enough to simply request that the bottom word should precede the top word on the same line in the cell.

[karedog]

I still don't understand the requirement. I don't see any changes in the second uploaded file compared to first one (I don't see the expected result).

Here I put 2 codes, maybe one of them is what you need.

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Karedog for all these great solutions ...
I will work on last code Test3 .. although it is not the desired result but it is more accurate than any other solution
I liked it a lot
Thank you very much for awesome and genius solution
Best Regards

[karedog]

You are welcome. If this is not the desired result, could you take the screenshot (instead of workbook) the result if you do it manually ?
Because when I open the file on post #6, there is only one sheet with content is exactly same as first post (beside the command button and the added macro).

[jindon]

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much Mr. Jindon
Here's a screen shot for the desired result
Untitled.png

But I need the two words in once cell one to the right and the other to left

Or another approach if this will be complicated to get the desired results (every word in single cell : cell to the right and the next cell to the left as in the screen shot) but th results would be after the last cell of column H (I mean in the same cell)

Thank you everybody for help

[karedog]

Doesn't my "Sub Test2()" do exactly what you mean ? Just widen column H (for example set column H width to 40) before run Sub Test2().

Link to animated gif :
http://s7.postimg.org/sry0zmlu3/Align_Right_Left.gif

[jindon]

Please Login or Register  to view this content.

[jindon]

For the safety, data like

Father's Job
وظيفة الأب"

Arabic comes after

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very very much for these wonderful solutions
In fact I liked all these solutions ..
@Mr. karedog the sub Test2 works very very well but I had to put a line of code to make the column width =40 at the end of the code to get the best results ..
It is working but the column width becomes too large in that case and I noticed extra line between the two words
Is it possible to adjust that a little to get rid of the extra line between...and make the column width less

@Mr. Jindon
Your last code in Post #17 is awesome ..
Just two points I need if possible :first to get the results starting at H7 which is the first balnk cell after last cell
Second to get the Arabic words first (if possible)

Thank you very much for supporting this issue..I am very grateful for both of you

Please Login or Register  to view this content.

[karedog]

@Mr. karedog the sub Test2 works very very well but I had to put a line of code to make the column width =40 at the end of the code to get the best results ..
It is working but the column width becomes too large in that case and I noticed extra line between the two words
Is it possible to adjust that a little to get rid of the extra line between...and make the column width less

In fact, setting column width must be done before you run sub Test2, and it is not problem at all if you don't adjust the column width.
The choose of column width is personal taste, that's why I don't include the code to set it.
There is no extra line when I run the code in my PC (tested on Excel 2003 and 2007), and there is no code to do so in sub Test2, so if there is an extra line on your PC, this is something strange, maybe caused by your Mid Eastern Office version ?
The result of my code on my PC can be looked at the link on post #15.

[YasserKhalil]

Thanks a lot for explaining the difference of version may be the casue .. and the evidence is that I found a way without using macro to do the task
By selecting range("H2:H6") and right click >> Format cells >> Alignment tab >> In Horizontal section select "Justify" ..it is working to some extent but it is vice versa :: the Arabic appears to the left edge (not the right edge) and the English word appears to the right edge(not the left edge)

Generally .. Can you help me in the other approach which is to split both words to single cells : the Arabic word in a cell and the English word in the next cell and so on ... and align Arabic to right edge and Englsih to the left

Here's the last desired results that I wish in the same column ....
Untitled.png

[jindon]

Please Login or Register  to view this content.

[karedog]

Done, by jindon

[YasserKhalil]

Thank you very much Mr. Jindon for this perfect solution. It is awesome and fascinating
Thanks a lot Mr. Karedog for all the great and wonderful solutions..

I am very happy with your help ..you ar GENIUS people
Best Regards