SIMPLE CONCATENATE FUNCTION
in [Functions]
|
Prev: Excel 2007: Vlookup Problem
Next: Can I increase the view of a drop down list beyond 8 in Excel?
From: Teethless mama on 9 Jan 2010 12:05 =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 9 Jan 2010 12:26 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 9 Jan 2010 18:23 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 9 Jan 2010 23:36 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 10 Jan 2010 19:55
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. |