How to simplify CONCATENATE

[Siva]

I am trying to select from a column of 30-40 cells (cells that are not blank)
and combine them into one cell. I am using concatenate but formula will be
too long.


Eg of my formula
=CONCATENATE(G2,"",G3,"",G4,"",G5,"",G6,"",G7,"",... G30)

Thanks

 

[Dave Peterson]

=g2&g3&g4&g5&...&g30

If you have to do this for lots of cells, you may want to use this function from
JE McGimpsey:
http://www.mcgimpsey.com/excel/udfs/multicat.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Siva]

Thanks Dave

 

[Colin Foster]

Hi Siva,

Try =G2&" "&G3&" "&G4 etc

.... and if in, for example, have a space in Cell H1 (i.e. select H1 & hit
the spacebar),

Then in H2 have the formula... =$H$1&G2
with =H2&$H$1&G3 in H3, then you can copy this down all the way to the
bottom of your data which in (say) H30 will have G2value <space> G3value
<space>...G30value

Try it, it makes more sense than me trying to type it!!

And don't forget that to quickly copy this formula down, double click on the
Black + sign that appears when you hover your mouse over the bottom right
corner of the cell

Hope this helps
Regards
Colin