Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Mail Merge to bullets question

    Hi, I am trying to merging text from a spreadsheet and wondering if there is anyway that where the text is followed by a full stop, when merged it would go to a bullet.

    So if in a cell (say A1) there was "text string.text string.text string." it would create 3 bullets followed by the text in the merge document.

    • Text Sting
    • Text Sting
    • Text Sting


    I hope that makes sense

    Regards

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    That is not possible - Word has no means of parsing strings that way via a mailmerge.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    I would be tempted to modify your spreadsheet datasource.

    In a VBA routine within excel try and use the Split() function to split the data at the periods.
    Then write the chunks to separate columns.
    Format the main merge document with a list bullet style.

    The following code segment demonstrates part of such a routine. It shows how to split your text at the "." and write to separate cells.

    Code:
    Sub test()
    '--------------------------------------------------------
    ' cell A2 would contain text string.text string.text string
    '--------------------------------------------------------
    Range("A2").Select
    ' sText = ActiveCell.Value
    ' If you  entered  text into A2
    ' you could read sText from the cell
     ' then delete line below
     
    sText = "text string.text string.text string"
    arText = Split(sText, ".")
    For i = 0 To UBound(arText)
    ActiveCell.Offset(0, i + 1).Value = arText(i)
    'Debug.Print arText(i)
    Next
    
    End Sub
    Is this type of approach viable?
    G

  4. The Following User Says Thank You to geofrichardson For This Useful Post:

    verada (2015-09-22)

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    If you can add another field in your Excel source you can add a formula to carry out the substitution and then use that field in your merge. A formula that replaces the character with a return looks like the following:
    =SUBSTITUTE(B2,".",CHAR(10))

    You might also be able to get there if you modify the query string in Word but I'm not 100% sure if that is possible and you will need to know some SQL if you wanted to go that way.
    Debug.print ActiveDocument.MailMerge.DataSource.QueryString
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    verada (2015-09-22)

  7. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi again
    Yet another approach

    Text To Columns
    You will find this on the data tab in Excel.

    This tip assumes that you can edit the Excel data source.

    Text To Columns enables you to split the contents of a single cell across multiple cells within the same row. The split is based on a specified delimiter. In your example the delimiter is the period.

    You nominate the cell address that marks the beginning of the output target range. Take care to avoid overwriting existing data.


    G
    Last edited by geofrichardson; 2015-09-19 at 04:05. Reason: clarification of output range

  8. The Following User Says Thank You to geofrichardson For This Useful Post:

    verada (2015-09-22)

  9. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Geoff: Do be aware that both the approaches you've suggested so far will wipe out any existing content in however many columns to the right of the one already holding the data the split data will be exported to. A little more finesse is required...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. The Following User Says Thank You to macropod For This Useful Post:

    verada (2015-09-22)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •