How do I copy the contents of a range of text cells and paste into one cell?

[davfin]

Hi,
Excel 2003 question:

I have a sheet that contains text in consecutive cells (in a column), I
need to copy all the consecutive cells' texts into one cell in a
separate spreadsheet.
Is there any quick way to do this? Just selecting the individual texts
and copy/pasting is time consuming and a pain. I have multiple
occurrences of this and need to find a quick way.

All help and suggestions very much appreciated.
Thanks,
Dave.

 

[Gord Dibben]

How many cells are you talking about and how much text is in them?

What you want can be done but may not be practical due to some limits in Excel.

Maybe some other method could be employed?

If not, use this UDF to stick the text into one cell....comma delimited as
written and ignores blank cells in the range.

If don't want a comma just delete that from the code.

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 & ","
'remove & "," if you don't need comma de-limited
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

On Sheet2 enter =ConCatRange(Sheet1!A1:A10)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula as instructed above.


Gord Dibben Excel MVP

Gord Dibben MS Excel MVP

Hi,
Excel 2003 question:

I have a sheet that contains text in consecutive cells (in a column), I
need to copy all the consecutive cells' texts into one cell in a
separate spreadsheet.
Is there any quick way to do this? Just selecting the individual texts
and copy/pasting is time consuming and a pain. I have multiple
occurrences of this and need to find a quick way.

All help and suggestions very much appreciated.
Thanks,
Dave.

Gord Dibben MS Excel MVP

 

[Max]

Just some thoughts ..
Select the columnar range > paste into Notepad
Then just select & copy the pasted range in Notepad,
back into Excel, click inside the formula bar of an empty cell > paste

 

[Max]

whoops, typo in line:

Select the columnar range > paste into Notepad

should read as:

Select the columnar range > copy & paste into Notepad

(missed out the "copy" part)

 

[davfin]

Hi Gord,
Tried it but no joy I'm afraid.
I have 4 columns with 139 rows, each cell has approx 20 characters in
it. I want to copy groups of cells in a column (anything up to approx
11 cells) into a single cell on a separate .xls spreadsheet (not a
sheet in the same workbook).

Dave.

 

[CaptainQuattro]

Use text concatenation formulas as follows:


Formula in Cell Sheet1!A1 of Book2:

=+[Book1]Sheet1!$A$2&[Book1]Sheet1!$A$3

Afterwards, convert everything to static values.

 

[davfin]

Max,
Thanks for the suggestion - as always the simple things are often the
best, it works a treat after you delete the space between the texts
while it's in Notepad (it contains the Excel formatting and will paste
into separate cells otherwise). It's then a simple case of inserting
spaces where needed once it's pasted into the new spreadsheet cell.

Thanks,
Dave.

 

[Max]

Glad it worked, Dave.
Thanks for the feedback ..