Converting a range of Excel 2003 Cells to a Single text string
 

Hello,

Would there be some way to use functions to convert a range of cells to a
text string without writing a macro for it.

I have tried the CONCATENATION function. But I would have type in each cell
address in it sepetated by commas. I cannot select a range for this function.

Thank you

Converting a range of Excel 2003 Cells to a Single text string
 

seems you have a choice:
use a formula referencing all the different cells, as
=CONCATENATE(A1, A2, A3)
or
=A1 & A2 & A3
You can get more cell references using the second method since you're not
limited to the 32 parameters that the CONCATENATE() function is.

OR
You can write a VB macro/user defined function to get the job done. Here's
one possible solution:

Public Function MakeLongString(anyRange As Range) As String
Dim individualCell As Range

For Each individualCell In anyRange
'strings them all together with a space between each
MakeLongString = _
MakeLongString & individualCell & " "
Next
'has an extra space at the end, get rid of it
MakeLongString = _
Left(MakeLongString, Len(MakeLongString) - 1)
End Function

That goes into a regular code module (press [Alt]+[F11] to open the VB
editor, choose Insert | Module to create a blank module and copy and paste
the code above into it).

To use it on the worksheet, enter a formula like
=MakeLongString(A1:K1)
where you have separate entries in cells A1, B1, C1 ... I1, J1 and K1 that
you want to appear as a single string.

Like any other worksheet function, you can use it in conjunction with other
worksheet functions as:
=MakeLongString(A1:K1) & B99 & MakeLongString(A9:A11)

that would take all entries in A1:AK and tack on the contents of cell B99 to
them and then also add on the contents of A9 through A11, displaying that
result in the cell.

Hope this helps you find a solution to your problem.



"JR2008" wrote:


Hello,

Would there be some way to use functions to convert a range of cells to a
text string without writing a macro for it.

I have tried the CONCATENATION function. But I would have type in each cell
address in it sepetated by commas. I cannot select a range for this function.

Thank you

Converting a range of Excel 2003 Cells to a Single text string
 

Thank you very much for the quick response.

"JLatham" wrote:

seems you have a choice:
use a formula referencing all the different cells, as
=CONCATENATE(A1, A2, A3)
or
=A1 & A2 & A3
You can get more cell references using the second method since you're not
limited to the 32 parameters that the CONCATENATE() function is.

OR
You can write a VB macro/user defined function to get the job done. Here's
one possible solution:

Public Function MakeLongString(anyRange As Range) As String
Dim individualCell As Range

For Each individualCell In anyRange
'strings them all together with a space between each
MakeLongString = _
MakeLongString & individualCell & " "
Next
'has an extra space at the end, get rid of it
MakeLongString = _
Left(MakeLongString, Len(MakeLongString) - 1)
End Function

That goes into a regular code module (press [Alt]+[F11] to open the VB
editor, choose Insert | Module to create a blank module and copy and paste
the code above into it).

To use it on the worksheet, enter a formula like
=MakeLongString(A1:K1)
where you have separate entries in cells A1, B1, C1 ... I1, J1 and K1 that
you want to appear as a single string.

Like any other worksheet function, you can use it in conjunction with other
worksheet functions as:
=MakeLongString(A1:K1) & B99 & MakeLongString(A9:A11)

that would take all entries in A1:AK and tack on the contents of cell B99 to
them and then also add on the contents of A9 through A11, displaying that
result in the cell.

Hope this helps you find a solution to your problem.



"JR2008" wrote:


Hello,

Would there be some way to use functions to convert a range of cells to a
text string without writing a macro for it.

I have tried the CONCATENATION function. But I would have type in each cell
address in it sepetated by commas. I cannot select a range for this function.

Thank you