Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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.


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I copy the contents of a range of text cells and paste into one cell?

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How do I copy the contents of a range of text cells and paste into

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


  #4   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How do I copy the contents of a range of text cells and paste

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
---
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I copy the contents of a range of text cells and paste into one cell?


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I copy the contents of a range of text cells and paste into one cell?


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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default How do I copy the contents of a range of text cells and paste into one cell?


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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How do I copy the contents of a range of text cells and paste

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I paste text data into ONE cell only? Pegcorp Excel Discussion (Misc queries) 1 May 10th 06 05:48 PM
copy paste delete cell contents Garry Excel Discussion (Misc queries) 0 March 27th 06 08:26 PM
how can we copy cells comments text and paste to cells שי פלד Excel Discussion (Misc queries) 3 December 12th 05 06:16 AM
How do I edit, copy and paste text in excel cells? floridactyl New Users to Excel 2 December 31st 04 04:52 PM
copy paste cell character limit Fred Excel Discussion (Misc queries) 1 December 2nd 04 09:58 PM


All times are GMT +1. The time now is 12:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"