SIMPLE CONCATENATE FUNCTION

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.

I am probably mis-understanding, but

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

HTH

Bob

"Rebecca" wrote in message
...
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.

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

--
Gary''s Student - gsnu200909


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

Put this is cell A 7:-

=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6)

If my comments have helped please hit Yes.

Thanks!

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

Type this into cell A 7:-

=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6)

If my comments have helped please hit Yes.

Thanks.


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

You could use something like this
In say, B1: =TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6)
The ampersand: & is the concat operator, shorter to type than CONCATENATE
TRIM is optional but recommended to remove any extraneous white spaces
Any joy? hit the YES below
--
Max
Singapore
---
"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.

Rebecca,

To make it readable, in addition to the words you need spaces.

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

Mike

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

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

"Rebecca" wrote in message
...
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.

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.

=A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6
--
Regards!
Stefi



„Rebeccaâ€� ezt Ã*rta:

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.