Hard line break displaying as rectangle on import from SQL
 

Hi,
Am pulling data from a company db via SQL and the hard line breaks in
several fields are coming is as that annoying little rectangle. Result is
that the report is pages and pages longer than it has to be. When try to use
the Replace function, Excel does not recognize the character. Any ideas on
how to control this short of going through the entire data set manually?

Thanks much for any ideas,

Hard line break displaying as rectangle on import from SQL
 

use a macro:

Sub FixData
Cells.Replace What:=chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End sub

if these are line return characters the could be Chr(10), chr(13) or both.

I would run the above. If some or all still remain, then replace the
chr(10) with chr(13) and run it again

If they are still there, then you will need to figure out what the character
is.

this can be done with a formula

=code(Mid(a1,12,1))
would give you the ascii code (chr number) for the 12 character in cell A1.

(or you could check that first before running the macro).

Obviously do testing on a copy of your data.

--
Regards,
Tom Ogilvy



"Martha" wrote:

Hi,
Am pulling data from a company db via SQL and the hard line breaks in
several fields are coming is as that annoying little rectangle. Result is
that the report is pages and pages longer than it has to be. When try to use
the Replace function, Excel does not recognize the character. Any ideas on
how to control this short of going through the entire data set manually?

Thanks much for any ideas,

Hard line break displaying as rectangle on import from SQL
 

this can be done with a formula

For the OP see also
http://www.cpearson.com/excel/CellView.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
use a macro:

Sub FixData
Cells.Replace What:=chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End sub

if these are line return characters the could be Chr(10), chr(13) or both.

I would run the above. If some or all still remain, then replace the
chr(10) with chr(13) and run it again

If they are still there, then you will need to figure out what the character
is.

this can be done with a formula

=code(Mid(a1,12,1))
would give you the ascii code (chr number) for the 12 character in cell A1.

(or you could check that first before running the macro).

Obviously do testing on a copy of your data.

--
Regards,
Tom Ogilvy



"Martha" wrote:

Hi,
Am pulling data from a company db via SQL and the hard line breaks in
several fields are coming is as that annoying little rectangle. Result is
that the report is pages and pages longer than it has to be. When try to use
the Replace function, Excel does not recognize the character. Any ideas on
how to control this short of going through the entire data set manually?

Thanks much for any ideas,