Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a hard break (alt enter) in a concatenated string | Excel Discussion (Misc queries) | |||
Replacing a hard line break in Excel | Excel Discussion (Misc queries) | |||
Break cell into multiple lines by line break | Excel Discussion (Misc queries) | |||
line break / hard return? | Excel Discussion (Misc queries) | |||
Hard Page Break | Excel Worksheet Functions |