Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-12-2017, 11:17 AM
alan100 alan100 is offline Remove Carriage Returns in Address Block Merge field Windows 10 Remove Carriage Returns in Address Block Merge field Office 2016
Novice
Remove Carriage Returns in Address Block Merge field
 
Join Date: Dec 2017
Posts: 3
alan100 is on a distinguished road
Default Remove Carriage Returns in Address Block Merge field

Hello to all,

I hope someone can perhaps please help with my problem.

I have exported some data that is in an access 2016 database to do a mailmerg.

One of the fields is an address "block", similar to below.

200 Any Street
AnyTown


PR23 4RT

This is in a mergefield "Address"

when I enter this merge field and complete the merge the "Address" field format is retained, I would like it to be all on one line as below;

200 Any Street AnyTown PR23 4RT

I have been looking for field codes or switches, unfortunately I cannot find what I need.

Could someone please offer some guidance

Thank you
Reply With Quote
  #2  
Old 12-12-2017, 02:14 PM
macropod's Avatar
macropod macropod is offline Remove Carriage Returns in Address Block Merge field Windows 7 64bit Remove Carriage Returns in Address Block Merge field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The answer is: don't use the address block field. Instead, insert the actual mergefields you want, where you want them.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-12-2017, 07:52 PM
alan100 alan100 is offline Remove Carriage Returns in Address Block Merge field Windows 10 Remove Carriage Returns in Address Block Merge field Office 2016
Novice
Remove Carriage Returns in Address Block Merge field
 
Join Date: Dec 2017
Posts: 3
alan100 is on a distinguished road
Default Updating Address format

hello

thank you for your reply.

Yes, my first thought was to change the address block format.

The data address block is sent to us in the current "block" format in an excel sheet that I have to import into Access (2016), when I try and change the format from the "wrap" format that it seems to be in, I then get problems trying to import the data in to access, access crashes and will not import it.

I tried removing the carriage returns using methods I have found including find and replace ctrl+J, but as mentioned I get errors when I try and import the data.

The address block could be typically between 3 and 6 lines so there is no standard format except that the last line will be the "Postcode" and the second to last line is the "Post Town", as such it just seemed easier to work with what I had, which was fine until I tried to do this merge which I must add the address in one line.

I can obviously remove the breaks manually after import but has I am looking at doing several hundred per day it would seem easier to remove the carriage returns.

I was just wondering if there was any other work around.

Thank you again for your reply
Reply With Quote
  #4  
Old 12-12-2017, 08:02 PM
macropod's Avatar
macropod macropod is offline Remove Carriage Returns in Address Block Merge field Windows 7 64bit Remove Carriage Returns in Address Block Merge field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You can't do that, which is why I said to insert the required mergefields where you want them. That way you won't need to make any post-merge changes.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-12-2017, 08:27 PM
alan100 alan100 is offline Remove Carriage Returns in Address Block Merge field Windows 10 Remove Carriage Returns in Address Block Merge field Office 2016
Novice
Remove Carriage Returns in Address Block Merge field
 
Join Date: Dec 2017
Posts: 3
alan100 is on a distinguished road
Default

As mentioned , there is only one merge field "Address" this one field has the complete address in it, all the lines, and is formatted with the carriage returns in it, so I cannot "place" it where I want it as you suggest.
Reply With Quote
  #6  
Old 12-12-2017, 08:32 PM
macropod's Avatar
macropod macropod is offline Remove Carriage Returns in Address Block Merge field Windows 7 64bit Remove Carriage Returns in Address Block Merge field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

So your source data already have the returns in the field? If so, there's nothing the mailmerge can do about that. You'll need to either modify the data source or edit the output post-merge. Personally, I'd be inclined to modify the data source, breaking the street address, locality, zip & state into separate fields - which is how such data are normally maintained.

Your original post's mention of an 'address "block"' led me to believe you were using the AddressBlock field.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
field codes, field switches, remove carriage return

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Carriage Return In Mailing Address Formd Mail Merge 1 07-27-2016 09:27 PM
Cant block own email address Barkas Outlook 0 06-20-2016 02:45 PM
Remove Carriage Returns in Address Block Merge field Run Script to remove carriage returns on certain columns ryanjohnsond@gmail.com Excel Programming 34 09-03-2014 10:43 PM
query returns garbage after 255 chars from a text field word_user Word VBA 0 06-02-2014 02:34 PM
makeup of the Address Block differentdrummer Mail Merge 2 12-01-2013 02:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft