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

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


"Rick" wrote:

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

On Oct 23, 10:49*am, Jacob Skaria
wrote:
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
---------------
Jacob Skaria

"Rick" wrote:
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.
--
Rick

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)

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


"Sandeep Warrier" wrote:

On Oct 23, 10:49 am, Jacob Skaria
wrote:
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
---------------
Jacob Skaria

"Rick" wrote:
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.
--
Rick

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

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


"Jacob Skaria" wrote:

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


"Rick" wrote:

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