Remove LF or CR from end of line. (1 Viewer)

pekajo

Registered User.
Local time
Tomorrow, 05:47
Joined
Jul 25, 2011
Messages
74
Hi,
There is something at the end of my fields (but only some so I cannot use mid ()) and it's not a space as was thinking it maybe a LF and/or CR.
Can anyone tell me how to remove LF or CR from end of line with vba.
Thanks
Peter
 

June7

AWF VIP
Local time
Today, 09:47
Joined
Mar 9, 2014
Messages
3,376
Can use Replace() function but quite possible there is some other non-printing character. Or try Left(x, Len(x)-1). Oops, you said only some so some records might not have this issue?

See if you can determine character code of last character in a query: Asc(Right(fieldname,1)). CR is 13, LF is 10
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:47
Joined
Jan 20, 2009
Messages
12,173
Replace(whatever, Chr(13),"")
 

pekajo

Registered User.
Local time
Tomorrow, 05:47
Joined
Jul 25, 2011
Messages
74
Did a ASC check as above and found it was a 160 and according to Google its 'ASCII 160 (non-breaking space), whatever that is'!!!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:47
Joined
Jan 20, 2009
Messages
12,173
Did a ASC check as above and found it was a 160 and according to Google its 'ASCII 160 (non-breaking space), whatever that is'!!!
It is a space that will not cause the text to wrap at it like an ordinary space does.
 

June7

AWF VIP
Local time
Today, 09:47
Joined
Mar 9, 2014
Messages
3,376
Interesting, Access drops normal trailing spaces but is retaining this non-breaking space. I would never have guessed. Are there other non-breaking spaces within the data? If not, just do Replace() to empty string. If there are, try doing a Replace() to change to normal space (32) and see if Access drops trailing space.

Where is data coming from?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
18,742
That would imply that the space was created by another program perhaps like Word, because the non-breaking space is not one of the characters normally associated with Access. And it doesn't sound familiar as an Excel output either.

However, the "Replace" function would allow you to use it similarly to Galaxiom's post #3. Instead of 13, use 160.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:47
Joined
Sep 21, 2011
Messages
7,497
I thought it was a HTML code? &nbsp
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
18,742
That could also explain it. We didn't get an explanation of the source of this text, after all. But a screen capture would make sense.
 

Users who are viewing this thread

Top Bottom