Splitting a line of text at commas

[Roderick O'Regan]

I have an Excel database and my Word template retrieves data from it
quite successfully.

One of the lines of data it retrieves is an address, each part of it
separated by commas e.g:
address1, address2, address3, and so on...

For historical reasons, the one line address must remain as it is in
the Excel database file.

My client has now asked me if I can split that line at each comma and
put each component on a separate line.

I'm sure it can be done but would not know where to start.

Is there a solution, please?

Roderick

 

[Jonathan West]

I have an Excel database and my Word template retrieves data from it
quite successfully.

One of the lines of data it retrieves is an address, each part of it
separated by commas e.g:
address1, address2, address3, and so on...

For historical reasons, the one line address must remain as it is in
the Excel database file.

My client has now asked me if I can split that line at each comma and
put each component on a separate line.

I'm sure it can be done but would not know where to start.

Is there a solution, please?

Roderick

Two possible approaches, depending on whether you insert the address in your
code as a string, or it is inserted by some other means e.g. mailmerge.

If it is a string, it is nice & easy - use the Replace function to replace a
comma (and probably the following space) with a vbCrLf

If the text is inserted by other means, then you need to identify the Range
where the text is, and do a Find on it, replacing ", " with "^p". Make sure
the Wrap property of the find is set to wdFindStop, so that the replace is
limited to your range.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Bear]

Roderick:

And if you need to manipulate the separate segments in your code, check out
the Split function. It's very handy. It puts the segments of a string into
separate members of an array, based on the delimiter you specify.

Bear

 

[Roderick O'Regan]

Thanks Jonathan and David (Bear).

Will now spend some time playing with this.

Hadn't met the Split function before, either.

Regards

Roderick

 

[Klaus Linke]

For completeness' sake, a fourth way:
Sometimes it's useful that you can turn a comma-separated file (from a
spreadsheet or database) into a Word table directly by choosing the comma as
the field separator:
Select the text, then
"Table > Convert > Text to Table, Separate text at... Other: ,".

Klaus

 

[Roderick O'Regan]

Thanks Klaus.

This is one I had met before and have used it to good effect.

Roderick