removing carriage returns and line feeds

[Miskacee]

I've import a document from Word to Access. However after it is imported
into Access, it has squares, which I'm assuming are carriage returns or line
feed marks. How do I get rid of these marks?

Thank you.

 

[Klatuu]

They may or may not be Carriage Returns or LineFeeds. They may be some other
character, so you need to be sure what they are. I assume you are importing
into a table, so open the table, find one field in a row you need to correct
and put it into a variable. Count how many characters from the beginning of
the variable the character is. Then, use the following to determine what it
is:
?asc(Mid(MyString,n,1)

13 = CR
10 = LF

Usually, they come in a pair 13 and 10. So, if that is the case, this will
strip them out:
replace(x,Chr(13)&chr(10),"")

 

[Ken Snell \(MVP\)]

Klatuus' suggestion for using Replace function is good. But note that Word
and Excel don't use the combination of a carriage return and line feed
characters for new lines. Excel uses just the line feed character, and I'm
not sure which one Word uses. So you will need to try Klatuu's suggestion
with just one of the Chr functions:

Replace(x, Chr(13), "")

or

Replace(x, Chr(10), "")

 

[Phillybanker]

Ken -

I've seen a number of your posts to this and similar issues and have tried
most but remain stumped.

I have exported contacts from Outlook into excel and the address fields have
hard returns. I needed to remove the hard return in order to convert text to
columns and split the address fields. Using =SUBSTITUTE(B1,CHAR(10),";"), I
was able to do so but there is still a square box [] in some of the cells.
When convert text to columns, all data after the square box is lost.

How do you remove the square box?

I have tried alt+0010 with a numeric keypad but that replaces all blank
spaces, not the square box.

Thoughts?

 

[Brendan Reynolds]

If I may be excused for jumping in ...

All of the commonly-encountered control characters have ASCII codes between
0 and 31, inclusive, so ...

Public Function RepNonPrint(ByVal InputString As Variant, ByVal Rep As
String) As String

Dim strWork As String
Dim lngLoop As Long

If IsNull(InputString) Then
RepNonPrint = vbNullString
Else
strWork = InputString
For lngLoop = 0 To 31
strWork = Replace(strWork, Chr$(lngLoop), Rep)
Next lngLoop
RepNonPrint = strWork
End If

End Function

Public Sub TestRepNonPrint()

Dim strTest As String

strTest = "some text" & Chr$(10) & _
"some more text" & Chr$(11) & _
"a third line" & Chr$(12) & _
"a fourth line" & Chr$(13) & _
"a fifth line"

Debug.Print "Before ..."
Debug.Print strTest
Debug.Print "After ..."
Debug.Print RepNonPrint(strTest, "~")

End Sub

Result, in the Immediate window ...

testrepnonprint
Before ...
some text
some more texta third linea fourth line
a fifth line
After ...
some text~some more text~a third line~a fourth line~a fifth line

--
Brendan Reynolds
Access MVP

Ken -

I've seen a number of your posts to this and similar issues and have tried
most but remain stumped.

I have exported contacts from Outlook into excel and the address fields
have
hard returns. I needed to remove the hard return in order to convert text
to
columns and split the address fields. Using =SUBSTITUTE(B1,CHAR(10),";"),
I
was able to do so but there is still a square box [] in some of the cells.
When convert text to columns, all data after the square box is lost.

How do you remove the square box?

I have tried alt+0010 with a numeric keypad but that replaces all blank
spaces, not the square box.

Thoughts?

Klatuus' suggestion for using Replace function is good. But note that
Word
and Excel don't use the combination of a carriage return and line feed
characters for new lines. Excel uses just the line feed character, and
I'm
not sure which one Word uses. So you will need to try Klatuu's suggestion
with just one of the Chr functions:

Replace(x, Chr(13), "")

or

Replace(x, Chr(10), "")