From: Teethless mama on
=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6)


"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.
From: T. Valko on
Another option is to download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

This add-in contains many useful functions including a range concatenation
function called MCONCAT.

The formula would be:

=MCONCAT(A1:A6," ")

--
Biff
Microsoft Excel MVP


"Rebecca" <Rebecca(a)discussions.microsoft.com> wrote in message
news:BEA2E257-D2CB-404A-A393-42AD346933E7(a)microsoft.com...
>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.


From: J_Knowles on
In cell A7

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

hth,
--
Data Hog


"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.
From: Rick Rothstein on
We can shorten your macro up by removing the loop...

Sub MakeSentence()
Dim MC As Long
MC = 1 'Column A
Cells(1, MC + 1).Value = Join(WorksheetFunction.Transpose( _
Range(Cells(1, MC), Cells( _
Rows.Count, MC).End(xlUp))), " ")
Columns(MC + 1).Columns.AutoFit
End Sub

--
Rick (MVP - Excel)


"Don Guillett" <dguillett1(a)austin.rr.com> wrote in message
news:ebWZjRTkKHA.2132(a)TK2MSFTNGP05.phx.gbl...
> Macro solution
>
> Option Explicit
> Sub makesentence()
> Dim mc As Long
> Dim i As Long
> Dim ms As String
> mc = 1 'col A
> For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
> ms = ms & Cells(i, mc) & " "
> Next i
> 'MsgBox ms
> Cells(1, mc + 1) = ms
> Columns(mc + 1).Columns.AutoFit
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "Rebecca" <Rebecca(a)discussions.microsoft.com> wrote in message
> news:BEA2E257-D2CB-404A-A393-42AD346933E7(a)microsoft.com...
>>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.
>

From: pdberger on
Rebecca --

=CONCATENATE(A1,B1,C1,D1,E1,F1)

Worked for me with similar OS & Excel

HTH

"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.