Use that long formula:
=A100&" "&A101&" " ... &A400&" "&A401&" "
but wrap trim around it:
=trim(A100&" "&A101&" " ... &A400&" "&A401&" ")

Trim will remove any leading/trailing/duplicated internal spaces.

For lots of cells, you may want to use a userdefined function:
http://mcgimpsey.com/excel/udfs/multicat.html
(From J.E. McGimpsey)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

nastech wrote:

060214 Copy column range of "single word" cells with spaces to a single cell

Hi, is there a formula or script for copying a range of cells, that have
single word-text, to a single cell with spaces between each word? Thanks!

have: =A100&" "&A101&" " ... &A400&" "&A401&" "
but have many records / unwanted space lines.
(possible answer to excluding "." periods might be using FIND-REPLACE
afterwards.

maybe something like:
=IF(OR(A1=".",A1=".blank."),then skip,A1:A3000)&" ")

not code literate, so if can include instruction like (enter your range
here, do this/that; can / cannot use with other code.. thanks)
have code from somewhere, but do not know how to use, if someone can expand
a little, thanks!!

x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
symbols = symbols & "+" & c

--

Dave Peterson