Pasting in paragraph of text, but with wordwrap?

[jbclem]

I'm using Excel 2002 and trying to paste in a paragraph of text into a spreadsheet (worksheet). The problem is that
it's showing up on a single line(row) that goes all the way to column CK. I tried formatting the beginning cell and
checked wordwrap but then what I got was the text in a long vertical column of 1 cell wide rows.

What I really would like is this paragraph of text looking like a real paragraph, 5 or 10 columns wide and word wrapped
inside the column limit. Is there a way to do this and could someone show me how?


Thanks....jc

 

[jbclem]

continuing...

I see that if I make a column real wide I can get the paragraph of text into it. But I want the text to stretch across
a number of preset columns that are already being used at a given width.

jc

 

[Earl Kiosterud]

jc,

You can merge a range of cells, both column and row-wise. Now it's one cell. Now set both
wrap text and merge cells. But don't be surprised if you have some weird problems later on,
with other things. Merging is generally avoided.

You'll probably also want to set the vertical alignment to top.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[David McRitchie]

Hi jc,
You can merge cells, I think that will solve your immediate problem.

Merging cells will cause other problems, with macros working on
a range of cells, with sorting, and inserting rows/columns depending on
what is merged.

Another problem that you might have had with your data is with
characters that you can't see
char(160) non breaking space character (  in HTML)
char(13) Carriage Return (CR)
char(10) Line Feed (LF)
char(13)&char(10) Carriage Return Line Feed (CRLF)
You might have to use the TrimALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
using the one found on the code section with more coding
http://www.mvps.org/dmcritchie/excel/code/join.txt
--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"jbclem" ...

 

[Gord Dibben]

Can you live with a macro?

Select the cell with text and a few cells to right and below then run this
macro.

Sub Make_Paragraph()
Dim cell As Range
If ActiveCell.Row = 1 Then GoTo warning
If Selection.Columns.Count = 1 Then _
If MsgBox(Prompt:= _
"You have only selected a single column. " & _
"Is this wide enough for your paragraph?", _
Buttons:=vbYesNo) = vbNo Then End
For Each cell In Intersect(Selection, _
Selection.Cells(1, 1).EntireColumn)
If Left(cell.Formula, 1) = " " And _
IsEmpty(cell.Offset(-1, 0)) Then _
cell.Formula = "zz|zz" & Mid(cell.Formula, 2)
Next
Application.DisplayAlerts = True
On Error Resume Next
Selection.Justify
On Error GoTo 0
Cells.Replace What:="zz|zz", Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Exit Sub
warning:
MsgBox "Activecell must not be in Row 1"
End Sub


Gord Dibben MS Excel MVP

 

[jbclem]

Thanks everyone for the quick replies...I think this puts me on the right path.

BTW, so glad to see you're top-posters! Some newsgroups make you feel like a visiting terrorist if you don't follow
their silly bottom-posting rules.

jc

 

[jbclem]

Hi Gord,

I've been trying to use this macro, and it works but it clips the text it's trying to wordwrap. I'm only getting about
half of the text that is wordwrapped, the rest just disappears. Is there a way to troubleshoot this problem?


jc

 

[Gord Dibben]

After testing it looks like any text beyond the 255 character is clipped.

I never used this macro on a cell with that much text so hadn't tested all
cases.

I don't know how to revise the macro which Bob Flanagan originally wrote to
accommodate more text.

Hopefully one of the not-so-VBA-challenged people can revise this one or come up
with soemthing better.

Meantime, I'll work on it.

Gord