Replace Carriage returns

K

Keith Ward

Hi,

I want to run the find and replace to do the following

......End of line.
Start of line.......

.......End of line. Start of line.......


I want to remove where the enter key has been pressed. How do I type it into
the search and replace.

Thanks

Keith
 
T

Tom Wickerath

Hi Keith,

Here are a couple of options for you:

1.) If your data is currently in Excel, and you want to clean it up before
importing into a table in Access, you can use the following macro:

Sub RemoveLineBreaks()
' Replaces unwanted line breaks with a comma & space
' From Inside Microsoft Excel, October 2002 issue
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim rng As Range
For Each rng In Selection
rng.Replace vbLf, ", "
rng.Replace vbCr, ", "
Next rng
Set rng = Nothing

End Sub

I have this macro in a Personal.xls file, which is saved in my XLStart
folder. I have assigned the shortcut key: Ctrl Shift R, so that this macro
will run in any Excel spreadsheet that I need to use it in.

Option # 2
If your data is already in Access, then I'd add a new field to the table and
run an update query to update the values in this new field. For instance,
suppose the name of the field that includes the carriage return + line feed
is "MemberName" in a table named "Members". If you are using Access 2000 or
greater, you can use the built-in Replace function as follows:

UPDATE Members
SET Members.NewMemberName =
Replace([MemberName],Chr(13) & Chr(10),",")
WHERE (((Members.MemberName) Is Not Null));

where NewMemberName is the new field that you add. This update query will
replace a carriage return + line feed with a comma. You might also choose to
replace with a comma + space, as in ", ".

If my answer has helped you, please consider signing in to Microsoft's
Online Community and mark my post as "Answered".

Tom
http://www.access.qbuilt.com/html/expert_contributors.html
___________________________________________

:

Hi,

I want to run the find and replace to do the following

......End of line.
Start of line.......

.......End of line. Start of line.......


I want to remove where the enter key has been pressed. How do I type it into
the search and replace.

Thanks

Keith
 
K

Keith Ward

Hi,

Access 200. Memo field contains

A clear and very accurate
Compass.
Designed for laser and
Other bits.
Red green blue.

I want

A clear and very accurate Compass.
Designed for laser and Other bits.
Red green blue.

I have tried

UPDATE Members
SET Members.NewMemberName = Replace([MemberName],".
","^^")
WHERE (((Members.MemberName) Is Not Null));

Using chr(13) or chr(10) does nothing.

The above changes the memo field to

A clear and very accurate
Compass^^Designed for laser and
Other bits^^Red green blue.

Then I do

UPDATE Members
SET Members.NewMemberName = Replace([MemberName],"
"," ")
WHERE (((Members.MemberName) Is Not Null));

And I get

A clear and very accurate Compass. Designed for laser and Other bits. Red
green blue.

(all on one line)

Where did my ^^ go so I can change them back.

Some text in the memo is supposed to be like bullet points, other like a
paragraph with sentences.

What's happening in the second iteration?

Thanks

Keith



Hi Keith,

Here are a couple of options for you:

1.) If your data is currently in Excel, and you want to clean it up before
importing into a table in Access, you can use the following macro:

Sub RemoveLineBreaks()
' Replaces unwanted line breaks with a comma & space
' From Inside Microsoft Excel, October 2002 issue
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim rng As Range
For Each rng In Selection
rng.Replace vbLf, ", "
rng.Replace vbCr, ", "
Next rng
Set rng = Nothing

End Sub

I have this macro in a Personal.xls file, which is saved in my XLStart
folder. I have assigned the shortcut key: Ctrl Shift R, so that this macro
will run in any Excel spreadsheet that I need to use it in.

Option # 2
If your data is already in Access, then I'd add a new field to the table and
run an update query to update the values in this new field. For instance,
suppose the name of the field that includes the carriage return + line feed
is "MemberName" in a table named "Members". If you are using Access 2000 or
greater, you can use the built-in Replace function as follows:

UPDATE Members
SET Members.NewMemberName =
Replace([MemberName],Chr(13) & Chr(10),",")
WHERE (((Members.MemberName) Is Not Null));

where NewMemberName is the new field that you add. This update query will
replace a carriage return + line feed with a comma. You might also choose to
replace with a comma + space, as in ", ".

If my answer has helped you, please consider signing in to Microsoft's
Online Community and mark my post as "Answered".

Tom
http://www.access.qbuilt.com/html/expert_contributors.html
___________________________________________

:

Hi,

I want to run the find and replace to do the following

......End of line.
Start of line.......

.......End of line. Start of line.......


I want to remove where the enter key has been pressed. How do I type it into
the search and replace.

Thanks

Keith
Click to expand...
 
T

Tom Wickerath

Some text in the memo is supposed to be like bullet points, other like a
paragraph with sentences.
Click to expand...

This sounds like your memo field includes rich text formatting. You didn't
mention this as a possibility in your initial post. Are you using some type
of rich text editing control in your database?

Tom
_______________________________________

Keith Ward said:
Hi,

Access 200. Memo field contains

A clear and very accurate
Compass.
Designed for laser and
Other bits.
Red green blue.

I want

A clear and very accurate Compass.
Designed for laser and Other bits.
Red green blue.

I have tried

UPDATE Members
SET Members.NewMemberName = Replace([MemberName],".
","^^")
WHERE (((Members.MemberName) Is Not Null));

Using chr(13) or chr(10) does nothing.

The above changes the memo field to

A clear and very accurate
Compass^^Designed for laser and
Other bits^^Red green blue.

Then I do

UPDATE Members
SET Members.NewMemberName = Replace([MemberName],"
"," ")
WHERE (((Members.MemberName) Is Not Null));

And I get

A clear and very accurate Compass. Designed for laser and Other bits. Red
green blue.

(all on one line)

Where did my ^^ go so I can change them back.

Some text in the memo is supposed to be like bullet points, other like a
paragraph with sentences.

What's happening in the second iteration?

Thanks

Keith



Hi Keith,

Here are a couple of options for you:

1.) If your data is currently in Excel, and you want to clean it up before
importing into a table in Access, you can use the following macro:

Sub RemoveLineBreaks()
' Replaces unwanted line breaks with a comma & space
' From Inside Microsoft Excel, October 2002 issue
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim rng As Range
For Each rng In Selection
rng.Replace vbLf, ", "
rng.Replace vbCr, ", "
Next rng
Set rng = Nothing

End Sub

I have this macro in a Personal.xls file, which is saved in my XLStart
folder. I have assigned the shortcut key: Ctrl Shift R, so that this macro
will run in any Excel spreadsheet that I need to use it in.

Option # 2
If your data is already in Access, then I'd add a new field to the table and
run an update query to update the values in this new field. For instance,
suppose the name of the field that includes the carriage return + line feed
is "MemberName" in a table named "Members". If you are using Access 2000 or
greater, you can use the built-in Replace function as follows:

UPDATE Members
SET Members.NewMemberName =
Replace([MemberName],Chr(13) & Chr(10),",")
WHERE (((Members.MemberName) Is Not Null));

where NewMemberName is the new field that you add. This update query will
replace a carriage return + line feed with a comma. You might also choose to
replace with a comma + space, as in ", ".

If my answer has helped you, please consider signing in to Microsoft's
Online Community and mark my post as "Answered".

Tom
http://www.access.qbuilt.com/html/expert_contributors.html
___________________________________________

:

Hi,

I want to run the find and replace to do the following

......End of line.
Start of line.......

.......End of line. Start of line.......


I want to remove where the enter key has been pressed. How do I type it into
the search and replace.

Thanks

Keith
Click to expand...
Click to expand...
 
Top