You can try one of the below formulas
=CONCATENATE(A1,A2,A3,A4,A5,A6)

=A1&A2&A3&A4&A5&A6

Concatenate do not work for a range. If you would like to try a UDF which
works for a range try this UDF (User Defined function). From workbook launch
VBE using Alt+F11. From menu Insert a Module and paste the below
function.Close and get back to workbook and try the below formula.

Syntax:
=CONCATRANGE(rngRange,strDelimiter,blnIgnoreBlank)
rngRange is the Range
strDelimiter Optional . Default is space
blnIgnoreBlank Optional. Default is False

Examples:
'1. Concatenate with default delimiter(space)
=CONCATRANGE(A1:A10)

'2. Concatenate with semicolon as delimiter and ignore blanks
=CONCATRANGE(A1:A10,":",1)

Function CONCATRANGE(rngRange As Range, _
Optional strDelimiter As String = " ", _
Optional blnIgnoreBlank As Boolean = False)
Dim varTemp As Range
For Each varTemp In rngRange
If blnIgnoreBlank Then
If Trim(varTemp) < vbNullString Then _
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
Else
CONCATRANGE = CONCATRANGE & strDelimiter & varTemp
End If
Next
CONCATRANGE = _
WorksheetFunction.Trim(Mid(CONCATRANGE, len(strDelimiter)+1))
End Function


--
Jacob


"Rebecca" wrote:

I apologize for asking such an incredibly newbie question, but I simply can't
get this to work, despite reading the help files and searching this forum. I
am using Windows 7 and newly purchased Excel 2007.

I have a column A with the following words (this is a simple, made-up
example): in

A1 The
A2 book
A3 is
A4 on
A5 the
A6 shelf.

I want to CONCATENATE them into one sentence in one cell. I can get
CONCATENATE to work in one row of cells in two or more columns, but I can't
get it to work in several rows of cells in the same column. Could you please
explain in detail how this can be done? Thanks.