Remove line break within cell
 

I have a document that was provided to me which I have to make some changes
to and save as a text file for importing into a database. However, there are
breaks after each line signified by a small square. I want to remove all
those breaks because when saving as a txt file, it thinks it is a real break.
How can I remove those crazy little squares.

You may need the drawing toolbar to select them.
Right click in any toolbar and select Drawing.
Right click in a toolbar and select CUSTOMIZE, Categories DRAWING,
Drag the "Select multiple objects" command to the drawing toolbar.
Click on CLOSE.
Click on the Select Multiple Objects tool and choose the objects you want to
delete.

Come back if that doesn't work.

"Tonya" wrote:

I have a document that was provided to me which I have to make some changes
to and save as a text file for importing into a database. However, there are
breaks after each line signified by a small square. I want to remove all
those breaks because when saving as a txt file, it thinks it is a real break.
How can I remove those crazy little squares.

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

If those box characters are char(10)'s (alt-enters), you can use
edit|Replace
what: ctrl-j
with: (spacebar??)
replace all

If that box character is something else, you may need a macro:

Option Explicit
Sub cleanEmUp()

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

myBadChars = Array(Chr(13)) '<--What showed up in CellView?

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

Tonya wrote:

I have a document that was provided to me which I have to make some changes
to and save as a text file for importing into a database. However, there are
breaks after each line signified by a small square. I want to remove all
those breaks because when saving as a txt file, it thinks it is a real break.
How can I remove those crazy little squares.

--

Dave Peterson

Thank you, thank you, thank you! The macro worked it was 13 :)

"Dave Peterson" wrote:

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

If those box characters are char(10)'s (alt-enters), you can use
edit|Replace
what: ctrl-j
with: (spacebar??)
replace all

If that box character is something else, you may need a macro:

Option Explicit
Sub cleanEmUp()

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

myBadChars = Array(Chr(13)) '<--What showed up in CellView?

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

Tonya wrote:

I have a document that was provided to me which I have to make some changes
to and save as a text file for importing into a database. However, there are
breaks after each line signified by a small square. I want to remove all
those breaks because when saving as a txt file, it thinks it is a real break.
How can I remove those crazy little squares.

--

Dave Peterson