REMOVING CARRIAGE RETURN 1

[itoldie]

Lately I have been receiving excel files with the address cell containing one address, a carriage return and another address. I need to export these excel files into access. Is there some way to remove this carriage return from excel before exporting or to remove them from access after importing? I have tried to remove them from access after importing them, but have not found a query which works at finding the carriage returns. I have also tried exporting the excel file into a text file as I found some code while searching Tek-Tips which removed carrige returns from a text file. However, the text file adds quotes to all the records that had a carriage return in them so that I would have had to remove those as well. Any help would greatly be appreciated.

 

[mgolla]

Have you tried using code to remove them?

Example:

Dim intCRLoc As Integer
Dim stTextBefore As String
Dim stTextAfter As String

intCRLoc = InStr(1, Text0, Chr(13))
stTextBefore = Left(Text0, intCRLoc - 1)
stTextAfter = Mid(Text0, intCRLoc + 2, Len(Text0) - (intCRLoc + 1))
Text0 = stTextBefore & " " & stTextAfter

This code could be used on a form to remove the Carriage Return from a field so if you were to create a recordset calling the field and then run thru the recordset using this code it could do the trick.

 

[itoldie]

Thanks mgolla. I found out from using your code that it was actually a line feed that was in excel and not a carriage return like I thought. So with a bit of tweeking, I was able to easily get rid of the unwanted character. It sure beats manually removing them.