Prev: Excel 2007: Vlookup Problem
Next: Can I increase the view of a drop down list beyond 8 in Excel?
From: Max on 9 Jan 2010 06:48
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.
From: Mike H on 9 Jan 2010 07:09
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.
From: Don Guillett on 9 Jan 2010 09:02
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: Jacob Skaria on 9 Jan 2010 10:15
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.
From: Stefi on 9 Jan 2010 11:03
=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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5 6 7
Prev: Excel 2007: Vlookup Problem
Next: Can I increase the view of a drop down list beyond 8 in Excel?