Combining text from several cells into 1 cell

[Rick]

I have text from a report that is spread over several cells. Each cell
contains 1 word. I want to bring all of these words into 1 cell, in Sheet1
A2. They come into Sheet1 in cells A1, B1, C1, D1, E1, F1, G1, H1. So A2
will contain all 8 words in 1 cell. Putting a space between words in A2 would
be best.

 

[Jacob Skaria]

Try the below formula in cell A2

=TRIM(A1 & " " & B1 & " " & C1 & " " & D1 & " " & E1 & " " & F1 & " " &
G1 & " " & H1)

Check help on CONCATENATE()

If this post helps click Yes

 

[Sandeep Warrier]

Try the below formula in cell A2

=TRIM(A1 & " " & B1  & " " & C1  & " " & D1  & " " & E1  & " " & F1  & " " &
G1  & " " & H1)

Check help on CONCATENATE()

If this post helps click Yes

Another option... a UDF...

Function ConcatRange(rRange As Range, Optional delim As String = " ")
As String
Dim rCell As Range
ConcatRange = ""
For Each rCell In rRange
ConcatRange = ConcatRange & rCell.Value & delim
Next rCell
ConcatRange = Left(ConcatRange, Len(ConcatRange) - Len(delim))
End Function

Use in A2 as =ConcatRange(A1:H1)

 

[Jacob Skaria]

If looking for a UDF; try the below..(From a previous post)

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)
'Jacob Skaria
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

If this post helps click Yes

 

[Rick]

Thank you so much for your help. This is just the solution I was looking for.