Mail Merge to bullets question

Topic

New Reply

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

Reply | Quote

Replies

That is not possible – Word has no means of parsing strings that way via a mailmerge.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote