concatenate and align ltr and rtl text

[mdavid]

Hi I need to concatenate 2 cells one Hebrew the other its English phonetic pronunciation.
I have the formula =A2&" "&B2 which works ok, but I also need to align the Hebrew text to the right and English to the left, so it's formatted correctly for both languages.
How can I do that?
Thanks for any help

 

[Hany ali]

without file ,we can't to help you ?!!!

 

[mdavid]

ok attached. Col I is Arabic, col J English and Col M concatinate of I and J

 

[vletm]

mdavid
Have tested this?

... then output is as below

 

[mdavid]

Thanks for this.
Not quite what I meant - English should be aligned left (start same position on left) and Arabic aligned right.
I think I need to take the max Length row and align all rows with that, Arabic right and English left - probably vba.

Thanks for any help
David

 

[vletm]

mdavid
Next version:

 

[mdavid]

Thanks vletm
Yes that's it, rows 3 -5 is what I want.
Excuse my ignorance, but can you explain the process.
Thanks
David

 

[vletm]

mdavid
1) select that column
2) I press Ctrl+1 to get 'Format Cell'-options
3) select 'Alignment'
4) select from 'Horizontal': Distributed ( as in that snapshot )
5) press [ OK ]
6) smile

 

[mdavid]

Hi Vletm, really appreciate this. One problem it's not working for me.
I've created a column with values (not fomula) of the concatinated English and Arabic values, selected the column and clicked ok on the attached format box - even smiled at it - but nothing happens.
Any ideas?
Thanks
David

 

[vletm]

mdavid

Mine above ... Your below ...
which has more options ... You gotta get that 'Indent' away ... hmm?
Is there SAME option as I have? if not then test with 'Justify distributed'???

 

[bosco_yip]

Maybe,

M2, formula copied down : =J2&REPT(" ",65-LEN(J2&I2))&I2

Font : Courier New

Alignment : Left and Wrap text

Regards
Bosco

 

[vletm]

mdavid
I would do it this way ...

 

[mdavid]

Hi thanks for your replies.
What I'm trying to do is prepare data for a website. I want to create one field from the Arabic and English phonetic and show the field in a listbox, so my question now is if I take Vietm's solution and when I create a csv file for upload to web I remove the "quotes comma quotes" between the two fields to create one string.
Would this solve my problem?
Really appreciate all your help, hope I haven't wasted your time - didn't think the problem through to the end.
Thanks
David

 

[vletm]

mdavid
You should explain in the beginning needed features ...
Excel > csv >> then You should again find out that align challenge.
722 rows in listbox ... it's Your choice ... but
- is it useful if one 'column'?
- is it useful if two 'columns'
Could You sit for few seconds and ... think?

 

[mdavid]

Yes, sorry. Problem is need to talk to see the end solution - my data starts in Excel, so that's all I thought about in the beginning.
The data on the website is divided into a page for each species so there's only 1 - 8 Arabic names per listbox - not 722
I think you've given me enough to solve this from here, maybe look at vba to build the string according to max length of fields per species.
Thanks for your help, learnt alot.
David