Hi
I have created an excel worksheet from a C#-program running on a sql-server.
In my C# program there are multiline textboxes which texts are stored in the
database. Once "exported" these columns to excel, squares are displayed
before every new line in a cell.

Looking at the same excel-sheet on another persons computer with the same
excel version, the squares are not there!

Is there any way of getting rid of these, except removing them manually?
I guess it must be since the squares are not displayed on my colleage's
excel.

It depends on what those squares are.

If they're alt-enters (char(10)'s), then just formatting the cells for wraptext
will make them force new lines within the cell.

If they're something else, you may need a helper cell with a formula or even a
macro to get rid of them or even edit|replace...

If you want to try edit|replace...

If it's alt-enter, you can hit and hold the alt key while typing 0010 on the
numeric keypad (or even just hit ctrl-j).

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

Once you know what it is, you could try the alt-#### to see if it works.

If it doesn't, you could use a macro to change it to a different character (|
maybe???). Then use that character in the data|Text to columns. (Import to one
column, change the character, and then do data|Text to columns).

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13))

myGoodChars = Array(" ")

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Helena wrote:

Hi
I have created an excel worksheet from a C#-program running on a sql-server.
In my C# program there are multiline textboxes which texts are stored in the
database. Once "exported" these columns to excel, squares are displayed
before every new line in a cell.

Looking at the same excel-sheet on another persons computer with the same
excel version, the squares are not there!

Is there any way of getting rid of these, except removing them manually?
I guess it must be since the squares are not displayed on my colleage's
excel.

--

Dave Peterson

Hi,
Thanks for your help!

The squares are for CHAR(13).
I have now in my stored procedure replaced CHAR(13) with CHAR(10) and at
least I have no squares left after that, but some users get squares also for
CHAR(10).

I wonder if there is a way to get rid of these squares without macros or
similiar?
Do you know if there is a setting in excel that makes those invisible? It's
so strange that some persons don't see the squares at all and some others
that see squares for CHAR(10) and some like me that only see squares for
CHAR(13).

Helena

"Dave Peterson" wrote:

It depends on what those squares are.

If they're alt-enters (char(10)'s), then just formatting the cells for wraptext
will make them force new lines within the cell.

If they're something else, you may need a helper cell with a formula or even a
macro to get rid of them or even edit|replace...

If you want to try edit|replace...

If it's alt-enter, you can hit and hold the alt key while typing 0010 on the
numeric keypad (or even just hit ctrl-j).

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

Once you know what it is, you could try the alt-#### to see if it works.

If it doesn't, you could use a macro to change it to a different character (|
maybe???). Then use that character in the data|Text to columns. (Import to one
column, change the character, and then do data|Text to columns).

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13))

myGoodChars = Array(" ")

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Helena wrote:

Hi
I have created an excel worksheet from a C#-program running on a sql-server.
In my C# program there are multiline textboxes which texts are stored in the
database. Once "exported" these columns to excel, squares are displayed
before every new line in a cell.

Looking at the same excel-sheet on another persons computer with the same
excel version, the squares are not there!

Is there any way of getting rid of these, except removing them manually?
I guess it must be since the squares are not displayed on my colleage's
excel.

--

Dave Peterson

Formatting the cell with wraptext will change the alt-enters (char(10)'s) to
linefeeds within the cell.

So it could be as simple as Format|cells|Alignment tab|check wraptext.

If those squares are caused by other characters, then sometimes Edit|replace
works (char(10) works, but char(13) doesn't).

Helena wrote:

Hi,
Thanks for your help!

The squares are for CHAR(13).
I have now in my stored procedure replaced CHAR(13) with CHAR(10) and at
least I have no squares left after that, but some users get squares also for
CHAR(10).

I wonder if there is a way to get rid of these squares without macros or
similiar?
Do you know if there is a setting in excel that makes those invisible? It's
so strange that some persons don't see the squares at all and some others
that see squares for CHAR(10) and some like me that only see squares for
CHAR(13).

Helena

"Dave Peterson" wrote:

It depends on what those squares are.

If they're alt-enters (char(10)'s), then just formatting the cells for wraptext
will make them force new lines within the cell.

If they're something else, you may need a helper cell with a formula or even a
macro to get rid of them or even edit|replace...

If you want to try edit|replace...

If it's alt-enter, you can hit and hold the alt key while typing 0010 on the
numeric keypad (or even just hit ctrl-j).

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

Once you know what it is, you could try the alt-#### to see if it works.

If it doesn't, you could use a macro to change it to a different character (|
maybe???). Then use that character in the data|Text to columns. (Import to one
column, change the character, and then do data|Text to columns).

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13))

myGoodChars = Array(" ")

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Helena wrote:

Hi
I have created an excel worksheet from a C#-program running on a sql-server.
In my C# program there are multiline textboxes which texts are stored in the
database. Once "exported" these columns to excel, squares are displayed
before every new line in a cell.

Looking at the same excel-sheet on another persons computer with the same
excel version, the squares are not there!

Is there any way of getting rid of these, except removing them manually?
I guess it must be since the squares are not displayed on my colleage's
excel.

--

Dave Peterson


--

Dave Peterson