Combining Cell Contents
 

Good afternoon. I know you can either use the concatenate function or the
formula =A1&B1 to combine cell contents.

However, if I am combining contents from 100 different cells, individually
selecting the cells become more cumbersome.

Is there a way to short cut this formula to pick up a range of cells I need
to combine the contents for?-- kind of how the reverse function of it works
for Text to Columns?

Thank you very much,
Storm

Combining Cell Contents
 

No shorthand method without some VBA.

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 & ","
'change the comma(",") to your choice of separator
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A100)

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 code in there.

Save the workbook and hit ALT + Q to return to Excel window

Enter the formula in a helper cell as explained above.

There is another VBA method which allows selecting non-contiguous cells and
placing into one cell.

See the macro I posted here at this URL

http://snipurl.com/1ge76


Gord Dibben MS Excel MVP

On Fri, 13 Apr 2007 13:22:03 -0700, Storm
wrote:

Good afternoon. I know you can either use the concatenate function or the
formula =A1&B1 to combine cell contents.

However, if I am combining contents from 100 different cells, individually
selecting the cells become more cumbersome.

Is there a way to short cut this formula to pick up a range of cells I need
to combine the contents for?-- kind of how the reverse function of it works
for Text to Columns?

Thank you very much,
Storm

Combining Cell Contents
 

WOW! Thank you very much Gord!

No one was responding so I tried to post it under the General Questions and
then I saw your message.

Thanks again for your help!
Storm

"Gord Dibben" wrote:

No shorthand method without some VBA.

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 & ","
'change the comma(",") to your choice of separator
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A100)

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 code in there.

Save the workbook and hit ALT + Q to return to Excel window

Enter the formula in a helper cell as explained above.

There is another VBA method which allows selecting non-contiguous cells and
placing into one cell.

See the macro I posted here at this URL

http://snipurl.com/1ge76


Gord Dibben MS Excel MVP

On Fri, 13 Apr 2007 13:22:03 -0700, Storm
wrote:

Good afternoon. I know you can either use the concatenate function or the
formula =A1&B1 to combine cell contents.

However, if I am combining contents from 100 different cells, individually
selecting the cells become more cumbersome.

Is there a way to short cut this formula to pick up a range of cells I need
to combine the contents for?-- kind of how the reverse function of it works
for Text to Columns?

Thank you very much,
Storm

Combining Cell Contents
 

Thanks for the feedback.


Gord Dibben MS Excel MVP

On Fri, 13 Apr 2007 15:44:03 -0700, Storm
wrote:

WOW! Thank you very much Gord!

No one was responding so I tried to post it under the General Questions and
then I saw your message.

Thanks again for your help!
Storm

"Gord Dibben" wrote:

No shorthand method without some VBA.

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 & ","
'change the comma(",") to your choice of separator
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A100)

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 code in there.

Save the workbook and hit ALT + Q to return to Excel window

Enter the formula in a helper cell as explained above.

There is another VBA method which allows selecting non-contiguous cells and
placing into one cell.

See the macro I posted here at this URL

http://snipurl.com/1ge76


Gord Dibben MS Excel MVP

On Fri, 13 Apr 2007 13:22:03 -0700, Storm
wrote:

Good afternoon. I know you can either use the concatenate function or the
formula =A1&B1 to combine cell contents.

However, if I am combining contents from 100 different cells, individually
selecting the cells become more cumbersome.

Is there a way to short cut this formula to pick up a range of cells I need
to combine the contents for?-- kind of how the reverse function of it works
for Text to Columns?

Thank you very much,
Storm

Combining Cell Contents
 

Copy your range paste it into notepad copy from a notepad select a
blank cell paste it into a formula bar not directly in a cell press enter


"Storm" wrote:

Good afternoon. I know you can either use the concatenate function or the
formula =A1&B1 to combine cell contents.

However, if I am combining contents from 100 different cells, individually
selecting the cells become more cumbersome.

Is there a way to short cut this formula to pick up a range of cells I need
to combine the contents for?-- kind of how the reverse function of it works
for Text to Columns?

Thank you very much,
Storm