How can I find, replace and delete line breaks?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
Click to expand...
replace 3 "lineBreaks" with two and run a few times
 
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))


Andi Mayer said:
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.
Click to expand...
replace 3 "lineBreaks" with two and run a few times
Click to expand...
 
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))
Click to expand...

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

whats in your data? both or only line feeds?
 
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>

Andi Mayer said:
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.
Click to expand...
replace 3 "lineBreaks" with two and run a few times
Click to expand...
 
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,


Andi Mayer said:
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))
Click to expand...

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

whats in your data? both or only line feeds?
Click to expand...
 
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

Ken Snell said:
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>

Andi Mayer said:
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.
Click to expand...
replace 3 "lineBreaks" with two and run a few times
Click to expand...
Click to expand...
 
Assuming that all the extra spaces between pars are caused by
superfluous chr(10) linefeeds, I tried this update query:
Click to expand...

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
 
Back
Top