Question on Linefeeds and Carriage returns...

[Guest]

Hi!

I have a problem that I hope someone will be able to help me with. I have an Access table that I exported to Excel. Some of the data has linfeeds in them separating small paragraphs. For some reason, it displays the linefeeds as single heavy vertical bars. I understand Access and Excel treat linefeeds differently (Excel uses CHR(10) and Access uses CHR(10) & CHR(13)), and I made sure it was converted back to what Excel requires before I transferred it. The funny thing is, it displays the linefeed correctly in the address bar. Then, when I click anywhere in the address bar of one of those suspect cells and then press enter, it displays the linefeed correctly in the cell. Any ideas???

Any help would be greatly appreciated.

Thanks a lot!

Dan

 

[Dave Peterson]

If you have format|cells|alignment|wrap text checked, then excel will use the
char(10) to wrap to a new line within the cell.

For me (arial font), the char(13) shows as a box--same as the char(10) without
wrap text enabled.

If you want to replace these characters with something else (space or
nothing???), you could use a macro to do the edit|replace's:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub


Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

Just keep adding those Hex codes you found using Chip's addin.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[David McRitchie]

Hi Dan,

CR CHR(13)
LF CHR(09)
CRFL CHR(13)&CHR(9)

If you are just going to remove them, I think I would
replace them with a space, after all there was some
kind of separation wanted there to begin with. And
then use Application.TRIM(xxx) to get rid of
spaces left, right, and excess spaces in-between

VBA TRIM does not touch excess spaces (duplicated)
spaces in-between, that's why I suggest the Excel Trim
instead.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

If you have format|cells|alignment|wrap text checked, then excel will use the
char(10) to wrap to a new line within the cell.

For me (arial font), the char(13) shows as a box--same as the char(10) without
wrap text enabled.

If you want to replace these characters with something else (space or
nothing???), you could use a macro to do the edit|replace's:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub


Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

Just keep adding those Hex codes you found using Chip's addin.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

the data has linfeeds in them separating small paragraphs. For some reason, it displays the linefeeds as single heavy vertical
bars. I understand Access and Excel treat linefeeds differently (Excel uses CHR(10) and Access uses CHR(10) & CHR(13)), and I made
sure it was converted back to what Excel requires before I transferred it. The funny thing is, it displays the linefeed correctly
in the address bar. Then, when I click anywhere in the address bar of one of those suspect cells and then press enter, it displays
the linefeed correctly in the cell. Any ideas???

 

[Dave Peterson]

I think chr(9) is the vbTab.

From the VBE immediate window:
?asc(vbtab)
9

?asc(vblf)
10

?asc(vbcr)
13

?len(vbcrlf)
2

?asc(mid(vbcrlf,1))
13

?asc(mid(vbcrlf,2))
10

Hi Dan,

CR CHR(13)
LF CHR(09)
CRFL CHR(13)&CHR(9)

If you are just going to remove them, I think I would
replace them with a space, after all there was some
kind of separation wanted there to begin with. And
then use Application.TRIM(xxx) to get rid of
spaces left, right, and excess spaces in-between

VBA TRIM does not touch excess spaces (duplicated)
spaces in-between, that's why I suggest the Excel Trim
instead.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

If you have format|cells|alignment|wrap text checked, then excel will use the
char(10) to wrap to a new line within the cell.

For me (arial font), the char(13) shows as a box--same as the char(10) without
wrap text enabled.

If you want to replace these characters with something else (space or
nothing???), you could use a macro to do the edit|replace's:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub


Chip Pearson has a very nice addin that can help identify those funny
characters.

http://www.cpearson.com/excel/CellView.htm

Just keep adding those Hex codes you found using Chip's addin.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

the data has linfeeds in them separating small paragraphs. For some reason, it displays the linefeeds as single heavy vertical
bars. I understand Access and Excel treat linefeeds differently (Excel uses CHR(10) and Access uses CHR(10) & CHR(13)), and I made
sure it was converted back to what Excel requires before I transferred it. The funny thing is, it displays the linefeed correctly
in the address bar. Then, when I click anywhere in the address bar of one of those suspect cells and then press enter, it displays
the linefeed correctly in the cell. Any ideas???

 

[David McRitchie]

You are correct (of course).

I think chr(9) is the vbTab.

 

[Dave Peterson]

And so is the immediate window!

<bg>

You are correct (of course).