Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text string back into a formula | Excel Discussion (Misc queries) | |||
Searching a text string in a range of cells. | Excel Worksheet Functions | |||
Converting Text String to Separate Numbers | Excel Discussion (Misc queries) | |||
converting a string of information into excel cells | Excel Worksheet Functions | |||
How to I copy text from a range of cells to another single cell? | Excel Discussion (Misc queries) |