Combining Cell Contents

[Guest]

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

 

[Gord Dibben]

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 Insert>Module. 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

 

[Guest]

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]

Thanks for the feedback.


Gord Dibben MS Excel MVP

 

[Guest]

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