How can I find, replace and delete line breaks?

[Guest]

Attempting to clean a large table in Access 2000, I've replaced thousands of
old HTML line-break tags with VBA line breaks, using an Update Query, as in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

With thanks,

Philip

 

[Andi Mayer]

Attempting to clean a large table in Access 2000, I've replaced thousands of
old HTML line-break tags with VBA line breaks, using an Update Query, as in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

replace 3 "lineBreaks" with two and run a few times

 

[Guest]

I've tried that. It doesn't seem to work.

Is this what you mean? --


Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))

Attempting to clean a large table in Access 2000, I've replaced thousands of
old HTML line-break tags with VBA line breaks, using an Update Query, as in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

replace 3 "lineBreaks" with two and run a few times

 

[Andi Mayer]

I've tried that. It doesn't seem to work.

Is this what you mean? --


Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))

chr(10)=line Feed
chr(13)=carriage return

whats in your data? both or only line feeds?

 

[Ken Snell [MVP]]

Pardon...I think it would be good to replace 2 "line break" combinations
with one "line break" so that all double/multiple line breaks are converted
to single ones.

--

Ken Snell
<MS ACCESS MVP>

Attempting to clean a large table in Access 2000, I've replaced thousands
of
old HTML line-break tags with VBA line breaks, using an Update Query, as
in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard
single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

replace 3 "lineBreaks" with two and run a few times

 

[Guest]

Andy,

Every space between pars in my database Text field is created by a carriage
return chr(13), followed by two linefeeds, chr(10) & chr(10). The combination
of these three inserts a space as follows:

But thousands of records have much larger spaces between pars, creating gaps
which look like this, or larger:



I presume this is because the carriage return is followed by more than two
linefeeds, chr(10). But I don't know if this is true, or if the extra spaces
between pars could contain both chr(13) and chr(10) codes. I have no idea how
to check which is the case.

Assuming that all the extra spaces between pars are caused by superfluous
chr(10) linefeeds, I tried this update query:

Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))

I figured this would replace three Chr(10)s with just two -- but it doesn't.

Is there a flaw in my query string?

Thanks,

I've tried that. It doesn't seem to work.

Is this what you mean? --


Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))

chr(10)=line Feed
chr(13)=carriage return

whats in your data? both or only line feeds?

 

[Guest]

Ken,

I've posted my message below to Andy, who tried to help me with this, but
I'm not sure how to make sure you see it as well -- except to send you
another copy (below).

The query string I used doesn't work, so I'm trying to find out where I went
wrong.

Thanks,

PAGA

Pardon...I think it would be good to replace 2 "line break" combinations
with one "line break" so that all double/multiple line breaks are converted
to single ones.

--

Ken Snell
<MS ACCESS MVP>

Attempting to clean a large table in Access 2000, I've replaced thousands
of
old HTML line-break tags with VBA line breaks, using an Update Query, as
in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard
single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

replace 3 "lineBreaks" with two and run a few times

 

[Tim Ferguson]

Assuming that all the extra spaces between pars are caused by
superfluous chr(10) linefeeds, I tried this update query:

Not tested, but this should do it:

Public Function RemoveLF(SomeString As String) As String
Dim p As Integer '
' find a Lf
p = Instr(SomeString,vbLf) ' vbLf is a Chr$(10)
If p = 0 then
' nothing to do
RemoveLF = SomeString

Else
' remove it, and any others
RemoveLF = RemoveLF(Left$(SomeString,p-1) & Mid$(SomeString,p+1))

End If

End Function


The other thing is to use the reg exp object to do it.

HTH


Tim F