Combining & formatting cells with text - Excel 2002

[Bob]

I have imported data from another application, SAP, into
a spreadsheet. There are 2 or more cells per row that
contain text.
I want to combine them into one cell with wrapped text to
be able to read easier or I would like to export all of
the text cells to Word without having it separated into
separate cells in word.
Any help would be appreciated.

 

[Gord Dibben]

Bob

In C1 enter =A1 & " " & B1

Copy C1 and(in place)Paste Special>Value>OK>Esc.

Set wrap text on C1.

Delete A1 and B1 if you want to.


Gord Dibben Excel MVP

 

[Bob]

Gord,

Thanks, that worked.

-----Original Message-----
Bob

In C1 enter =A1 & " " & B1

Copy C1 and(in place)Paste Special>Value>OK>Esc.

Set wrap text on C1.

Delete A1 and B1 if you want to.


Gord Dibben Excel MVP



.

 

[Bob]

Is there a limitation as to how many cells you can do
this with?
Thanks for your patience.

 

[Gord Dibben]

Don't know Bob.

I have never tried to find a limit using that method.

The CONCATENATE Function which does the same thing(without the spaces) has a
limit of 30 cells which most Functions are limited to.

=CONCATENATE(A1,A2,A3...A30) is the limit.

A User Defined Function such as the one below takes many cells.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A100)

Note: you can have 32767 characters in a cell but Excel will show or print
only 1024.

Adding a few ALT + ENTERS at appropriate spots will increase the "show" limit.


Gord