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

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

Gord Dibben Wrote:
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 InsertModule. 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
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


--
davfin
------------------------------------------------------------------------
davfin's Profile: http://www.excelforum.com/member.php...o&userid=36012
View this thread: http://www.excelforum.com/showthread...hreadid=557961

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.


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=557961

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


--
davfin
------------------------------------------------------------------------
davfin's Profile: http://www.excelforum.com/member.php...o&userid=36012
View this thread: http://www.excelforum.com/showthread...hreadid=557961

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.