locked
Mail merge from Excel 2010 to Word 2010 when some fields contain Hebrew messes up parenthesis in English-only fields RRS feed

  • Question

  • I have been trying to do a mailmerge from Excel into Word (Office 2010 versions).  The Excel file contains some columns in Hebrew, while the rest are in English.  Some of the English fields contain entries of the form "123 (comment)" (every character in this string is stored in the ASCII character range, as I've confirmed by unpacking the xlsx file and examining the xml manually).

    However, when I merge into Word, some instances of these fields are rendered as ")123comment (" (or thereabouts) where the parentheses have been incorrectly labelled as being right-to-left text.  This is consistent between runs, and different records behave differently with respect to the same fields (so some records will end up with "123 (comment)" while others have ")123comment ").

    Is there any way to tell Word 2010 that "This field is left-to-right text - don't mess with it!"?

    Thanks,

    Julian
    Friday, November 2, 2012 12:31 PM

Answers

All replies

  • Hi,

    Is the field format set as Text?

    And maybe while mail merging, there is no way to tell Word that "This field is left-to-right text - don't mess with it!"….But after mail merging, maybe some macro can do and change back the parenthesis.


    Jaynet Zhang
    TechNet Community Support
    A new Office has arrived, try it now.
    A beautiful Start. It begins here. Windows 8 and Windows RT.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jaynet Zhang Thursday, November 8, 2012 2:30 PM
    Monday, November 5, 2012 9:24 AM
  • 1. I was able to reproduce this behaviour (or something very similar) here.

    2. I believe that the "English" text is being treated as RTL text and that the algorothm that processes mixed RTL/LTR text is in effect deciding that the text is in three "runs":

     a. 123 opening brace, which displayed in RTL is

    "RTL opening brace" 123 

    i.e.

    ) 123

     b. comment, which is actually LTR

     c. RTL closing brace, i.e.

    (

    2. In Word, if I use Insert->Symbol->More Symbols...->Special characters to insert a Left-toRight Override charater immediately before the Mailmerge field that inserts this field, the first "run" is reversed, but the third run is not. So I see

    123 (comment(

    3. Nothing else I have tried so far in Word has improved on that (although I can think of a few things that might make a difference, but I've run out of time for now.  The main thing I haven't really tried is to modify the SQL that gets the data from Excel.

    4. In Excel, when I first looked at the Comment texts, the Cell/Paragraph direction was set to "Context", but setting them to LTR made no difference. Putting a Left-to-Right override character at the beginning of the cell, or even immediately before the "(" made some difference, but again, did not fix the final ")"

    5. When I first looked at the Edit Recipients dialog box in Word and examined the text in VBA using MailMerge.Datasource.Datafields("TheFieldName").Value  there was no sign of any LTR/RTL markup. When I saved the Word document and looked at the underlying XML, I could see that the first and third runs were marked as RTL. This all suggested that the problem was at the Word end.

    6. However, I then realised that Word got it wrong when the cell in the previous column contained only Hebrew text, and got it right when the text was "English only" or mixed. So I added a column to Excel immediately before the Comments column, and just put the letter "a" in every cell in the column. That seemed to fix the problem here, but 

     a. I am only using very simple test data

     b. I haven't yet found out exactly what that column has to contain (e.g. it might have to have all the cells formatted as LTR as well)

    7. This suggests to me that the problem is actually at the Excel end, but it really is quite difficult to be sure!


    Peter Jamieson

    • Proposed as answer by Dave La Sunday, July 12, 2020 7:06 PM
    Saturday, November 10, 2012 11:50 PM
  • 6. However, I then realised that Word got it wrong when the cell in the previous column contained only Hebrew text, and got it right when the text was "English only" or mixed. So I added a column to Excel immediately before the Comments column, and just put the letter "a" in every cell in the column. That seemed to fix the problem here, but 

     a. I am only using very simple test data

     b. I haven't yet found out exactly what that column has to contain (e.g. it might have to have all the cells formatted as LTR as well)

    7. This suggests to me that the problem is actually at the Excel end, but it really is quite difficult to be sure!


    Peter Jamieson

    I see this issue a long time, and tried to figure the problem without success, thanks Peter for clarifying it.

    Is there any other solutions besides adding a letter in the other column?

    Sunday, July 12, 2020 7:11 PM
  • Well, I haven’t looked at this since 2012. I can have another look. Which version(s) of Word are you using?

    Peter Jamieson

    Monday, July 13, 2020 5:51 AM
  • The version is 2019 but i had this with the previous versions also.
    Monday, July 13, 2020 1:43 PM
  • Working with Word 365 here, I haven't investigated as thoroughly as before, because my latest experiments suggest that

     a. the problem occurs when the "English language" text in the data source looks like "123 (something)" but not when the text looks like "abc (something)" or "1bc (something)"

     b. Inserting a Left-to-Right mark immediately before a mergefield that you expect to contain LTR text does *not* help

    c. Inserting either a Left-to-Right Embedding or a Left-to-Right override *does* help. 

     d. If you are in a position to set up the connection to your data source using VBA, for an *Excel* source, adding a "dummy" column with "English" text in it seems to help.

    e.g. on point (d), if you have an excel sheet with columns k, ht1 (RTL text), rt1 (LTR text), then using VBA like this seems to help:

    ActiveDocument.MailMerge.OpenDataSource _
      Name:="the path+file name of your Excel data source", _

      SQLStatement:="SELECT k ,ht1, 'x' As [rt0], rt1 FROM [sheetname$]"

    i.e. you insert a column containing a single LTR letter before each column that you want to be treated as LTR.

    However, that requires that you know exactly what columns you have and which ones are supposed to be LTR, and that you do not have more columns than the Jet/ACE provider can retrieve (255 ish), and that you can fit the necessary SQL into the maximum length allowed by OpenDataSource.

    Going back to points (a), (b) and (c) , to be honest, it's really hard to be confident about the effect of LTR/RTL mark/embed/override characters *in general* without doing a lot more experiments, and even then I am conscious of the fact that I am only testing with simple "made-up" data. What I really, really miss in these groups is any sense that people are willing to "crowdtest" all kinds of possibilities.

    When I first looked at this, I suspect I would have looked at the cases where the data contained *a number* followed by (something) and probably not noticed the difference between that case and the case where the data contained *a non-number* followed by (something). So it seems that the problem of *numbers being in LTR sequence even within RTL sequences" may also be a factor.


    Peter Jamieson


    Tuesday, July 14, 2020 8:56 PM