Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi,
Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&" "&A6&" "&....... and so on till A45. I'm tired of writing till A45, hence, did not write it out fully. Is there any Excel formula / technique using by which one can specify only the first cell (A1) and the last cell (A45 in this case) and get the concatenate without pains. (If you are curious as to what I do with the concatenation result...- I use excel for preparing job files for a Cross tabulation program, hence use excel as an intermediary to simplify the process of making jobs) Thanks a lot, Hari India |
#2
![]() |
|||
|
|||
![]()
Hari,
I had a similar need once so I wrote a macro to do it. Just select the first cell in the row, and run the macro, it concatenates it all into that active cell Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell End With End Sub If you want to clear out the other stuff Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell Range(.Offset(0, 1), Cells(.Row, cLastCol)).ClearContents End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Hari" wrote in message ... Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&" "&A6&" "&....... and so on till A45. I'm tired of writing till A45, hence, did not write it out fully. Is there any Excel formula / technique using by which one can specify only the first cell (A1) and the last cell (A45 in this case) and get the concatenate without pains. (If you are curious as to what I do with the concatenation result...- I use excel for preparing job files for a Cross tabulation program, hence use excel as an intermediary to simplify the process of making jobs) Thanks a lot, Hari India |
#3
![]() |
|||
|
|||
![]()
Hi Bob,
Thnx a lot for the macro. It WORKS well. Just a small clarification. If the active cell has "wert" then there are 2 instances of "wert" in the starting. Basically the first cell is concatenated with itself... Though deleting that second occurrence is no problem (as compared to writing a long concatenate) , just wanted to understand how to programmatically come over this. Thanks a lot, Hari India "Bob Phillips" wrote in message ... Hari, I had a similar need once so I wrote a macro to do it. Just select the first cell in the row, and run the macro, it concatenates it all into that active cell Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell End With End Sub If you want to clear out the other stuff Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell Range(.Offset(0, 1), Cells(.Row, cLastCol)).ClearContents End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Hari" wrote in message ... Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&" "&A6&" "&....... and so on till A45. I'm tired of writing till A45, hence, did not write it out fully. Is there any Excel formula / technique using by which one can specify only the first cell (A1) and the last cell (A45 in this case) and get the concatenate without pains. (If you are curious as to what I do with the concatenation result...- I use excel for preparing job files for a Cross tabulation program, hence use excel as an intermediary to simplify the process of making jobs) Thanks a lot, Hari India |
#4
![]() |
|||
|
|||
![]()
Hari
One more routine for you. Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben Excel MVP On Mon, 3 Jan 2005 19:14:19 +0530, "Hari" wrote: Hi Bob, Thnx a lot for the macro. It WORKS well. Just a small clarification. If the active cell has "wert" then there are 2 instances of "wert" in the starting. Basically the first cell is concatenated with itself... Though deleting that second occurrence is no problem (as compared to writing a long concatenate) , just wanted to understand how to programmatically come over this. Thanks a lot, Hari India "Bob Phillips" wrote in message ... Hari, I had a similar need once so I wrote a macro to do it. Just select the first cell in the row, and run the macro, it concatenates it all into that active cell Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell End With End Sub If you want to clear out the other stuff Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell Range(.Offset(0, 1), Cells(.Row, cLastCol)).ClearContents End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Hari" wrote in message ... Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&" "&A6&" "&....... and so on till A45. I'm tired of writing till A45, hence, did not write it out fully. Is there any Excel formula / technique using by which one can specify only the first cell (A1) and the last cell (A45 in this case) and get the concatenate without pains. (If you are curious as to what I do with the concatenation result...- I use excel for preparing job files for a Cross tabulation program, hence use excel as an intermediary to simplify the process of making jobs) Thanks a lot, Hari India |
#5
![]() |
|||
|
|||
![]()
Hi Hari,
Fix that problem. Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column ' line below changed For Each cell In Range(.Offset(0,1), Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell End With End Sub This is the version that doesn't clear out the cells. -- HTH RP (remove nothere from the email address if mailing direct) "Hari" wrote in message ... Hi Bob, Thnx a lot for the macro. It WORKS well. Just a small clarification. If the active cell has "wert" then there are 2 instances of "wert" in the starting. Basically the first cell is concatenated with itself... Though deleting that second occurrence is no problem (as compared to writing a long concatenate) , just wanted to understand how to programmatically come over this. Thanks a lot, Hari India "Bob Phillips" wrote in message ... Hari, I had a similar need once so I wrote a macro to do it. Just select the first cell in the row, and run the macro, it concatenates it all into that active cell Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell End With End Sub If you want to clear out the other stuff Sub JoinData() Dim cLastCol As Long Dim cell As Range With ActiveCell cLastCol = Cells(.Row, Columns.Count).End(xlToLeft).Column For Each cell In Range(ActiveCell, Cells(.Row, cLastCol)) .Value = .Value & " " & cell.Value Next cell Range(.Offset(0, 1), Cells(.Row, cLastCol)).ClearContents End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Hari" wrote in message ... Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&" "&A6&" "&....... and so on till A45. I'm tired of writing till A45, hence, did not write it out fully. Is there any Excel formula / technique using by which one can specify only the first cell (A1) and the last cell (A45 in this case) and get the concatenate without pains. (If you are curious as to what I do with the concatenation result...- I use excel for preparing job files for a Cross tabulation program, hence use excel as an intermediary to simplify the process of making jobs) Thanks a lot, Hari India |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
How do I dynamically retrieve the cell address of the last cell t. | Excel Discussion (Misc queries) | |||
CELLS HAVING SAME NUMBER BY CHANGING ANY CELL | Excel Discussion (Misc queries) |