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

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.

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

On Mon, 3 Jul 2006 20:49:06 -0500, davfin
<davfin.2aee5b_1151977801.7485@excelforum-nospam.com> wrote:

>
>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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"davfin" wrote:
>
> 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.
>
>
> --
> davfin
> ------------------------------------------------------------------------
> davfin's Profile: http://www.excelforum.com/member.php...o&userid=36012
> View this thread: http://www.excelforum.com/showthread...hreadid=557961
>
>

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

Originally Posted by 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

On Mon, 3 Jul 2006 20:49:06 -0500, davfin
<davfin.2aee5b_1151977801.7485@excelforum-nospam.com> wrote:

>
>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

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.

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.

Originally Posted by 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad it worked, Dave.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"davfin" wrote:
>
> 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.