Carriage return on "Import External Data"

[Guest]

I am using "Import External Data" to read data from a SQL Server database. Some of the fields I am reading are SQL server data type "text" and the application that maintains this data allows users to enter carriage returns into the "text" fields. The problem I am having is that on importing this data, any carriage returns input by the user are being represented in Excel as a small square character. Does anyone know how to omit this character on import

Thanks in advance.

 

[Dennis]

Craig,

After import of the data, insert a column next to column
(s) that have CR's in them i.e. B1

In A1, enter Trim(Clean(B1)) then copy the formula down.

Next, do a Copy>Paste Special>Values Only to A1 (down).

Then delete column B.

I am sure that those with VBA will have even a better
solution.

Happy Ney Year, Dennis

-----Original Message-----
I am using "Import External Data" to read data from a

SQL Server database. Some of the fields I am reading are
SQL server data type "text" and the application that
maintains this data allows users to enter carriage
returns into the "text" fields. The problem I am having
is that on importing this data, any carriage returns
input by the user are being represented in Excel as a
small square character. Does anyone know how to omit
this character on import.

 

[Peter Atherton]

-----Original Message-----
I am using "Import External Data" to read data from a SQL

Server database. Some of the fields I am reading are SQL
server data type "text" and the application that maintains
this data allows users to enter carriage returns into
the "text" fields. The problem I am having is that on
importing this data, any carriage returns input by the
user are being represented in Excel as a small square
character. Does anyone know how to omit this character on
import.

Thanks in advance.
.

Craig

Run the following code:

Sub ClearReturns()
Dim c As Variant
For Each c In Selection
c.Replace What:=Chr(10), Replacement:="",
LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
Next c
End Sub

it works on a selection = see my post to Mani
Edit No Format to see how to apply this to a particula
range

Regards
Peter