• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Replace ", " with Return and Line Feed - Replace Function Microsoft Access

Hi all,

I am importing a list of addresses into an Access table and a typical address is:
100 High Street, Some Town, Some County, Some Postcode

I need to use the replace function to get it to look like:
100 High Street
Some Town
Some County
Some Postcode

Am happy to replace function at query level to "a" with "b", but not sure how to handle the above regarding Returns and Linefeeds, Thanks
  • 2
1 Solution
Use this In an update query:

UPDATE YourTable
SET YourField = Replace(YourField, ",", Chr(10) & Chr(13))

Make a backup before testing any action query.
Sorry - I've got that backwards.  It should be:

UPDATE YourTable
SET YourField = Replace(YourField, ",", Chr(13) & Chr(10))

Chr(13) & Chr(10)  is Carriage Return/Line Feed (the equivalent of vbCRLF in VBA programming)

You might also try this - which may avoid extraneous spaces in your data after breaking the lines apart (it replaces 'comma space' with CR/LF):

UPDATE YourTable
SET YourField = Replace(YourField, ", ", Chr(13) & Chr(10))
Paul-bbcAuthor Commented:
Thanks, worked fine, just needed to amend to ", " i.e. include the space character to be replaced as well.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now