SIMPLE CONCATENATE FUNCTION

[Rebecca]

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.

 

[Bob Phillips]

I am probably mis-understanding, but

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

HTH

Bob

 

[Gary''s Student]

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

 

[trip_to_tokyo]

Put this is cell A 7:-

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

If my comments have helped please hit Yes.

Thanks!

 

[trip_to_tokyo]

Type this into cell A 7:-

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

If my comments have helped please hit Yes.

Thanks.

 

[Max]

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

 

[Mike H]

Rebecca,

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

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

Mike

 

[Don Guillett]

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

 

[Jacob Skaria]

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

 

[Stefi]

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



„Rebecca†ezt írta:

 

[Teethless mama]

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

 

[T. Valko]

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," ")

 

[J_Knowles]

In cell A7

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

hth,

 

[Rick Rothstein]

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

 

[pdberger]

Rebecca --

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

Worked for me with similar OS & Excel

HTH

 

[ryguy7272]

Maybe this:
=A1&A2&A3&A4&A5&A6

Select the range and hit F4, you'll get this:
=$A$1&$A$2&$A$3&$A$4&$A$5&$A$6

Look in the help files, or on Google, for 'Absolute Reference'.

 

[Ron@Buy]

Rebecca don't quite understand your problem. Presumably your using:
=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6)
or
=A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6
Please explain how this will not work in any cell that you copy it to !?

 

[JLatham]

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

 

[Eduardo]

Hi,
try

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

if this helps please click yes thanks

 

[Eduardo]

Hi, try

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