Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hari
 
Posts: n/a
Default Concatenate cells without specifying/writing cell address individually

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Hari
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
How do I dynamically retrieve the cell address of the last cell t. Nancy Excel Discussion (Misc queries) 1 December 20th 04 03:52 PM
CELLS HAVING SAME NUMBER BY CHANGING ANY CELL JOHN MORREY Excel Discussion (Misc queries) 2 December 2nd 04 03:50 AM


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"