Combining Cell Contents

  • Thread starter Thread starter Guest
  • Start date Start date
G

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