formatting text in cell

[lbockhed]

hi

i have some text copied into the cells on my spreadsheet

the text is in the format:

"textextextext

textextextexte

textextex"

another words there are paragraphs (line breaks) in the text

is it possible to format the text in some way to make it continuous, with no
paragraphs lines, just one continuous piece of txt, in the format:

"textextextexttextextextextetextextex"

i have a few hundred cells like this and dont want to go through it all
manually, using delete to make it into the format i want

 

[Peo Sjoblom]

You could try this

First the text wrap and remove checkmark under format>cells>alignment, wrap
text
Then do edit>replace, hold down alt and type 010 on the numpad in the
replace what box, put a space in the replace with box

 

[lbockhed]

Thanks

I'm getting problems

1) excel could not find a match
2) formula too long

any ideas?

 

[Dave Peterson]

How about a macro:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Select some cells that have Text"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = Replace(myCell.Value, Chr(10), " ")
Next

End Sub

If you're using xl97, change that Replace( to application.substitute(

(and I use " " as the replacement)

If you have carriage returns (alt-0013), change that chr(10) to chr(13).


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