View Full Version : Concatenate two columns in a sheet
sindhuja
03-26-2012, 09:29 PM
Hi,
I need to concatenate the two columns (R and S) in sheet 1 and results to be displayed in column T.
i want a new column to be inserted using coding and display the concatenated results in that column. Once this is done the format to be changed as "dd/mm/yyyy" since the column R and S are date values.
Sub ConcatenateRange()
Dim val As String
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long
Dim j As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("P1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
val = ""
For j = 2 To iLastCol
val = val & Cells(i, j)
Next j
Cells(i, "R").Value = val
Next i
End Sub
used the coding above, as i need to concatenate only the values in column Q and R i tried editing the coding.. but am not sucessful..
pls help me on this...
-Sindhuja
mancubus
03-26-2012, 11:39 PM
Sub ConcatCols()
'concatenate columns R & S in column T
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "R").End(xlUp).Row
With .Range("T2:T" & LastRow)
.Formula = "=R2&S2"
.Value = .Value
End With
End With
End Sub
sindhuja
03-28-2012, 02:32 AM
I need to format the values in column T as "DD/MM/YYYY"
Help me with this also..
Bob Phillips
03-28-2012, 02:41 AM
.Range("D2").Resize(LastRow - 1) .Numberformat = "dd/mm/yyyy"
Beatrix
07-24-2012, 09:05 AM
Hi Mancubus ,
I was going to use the code you provided in your previous reply below; but it didn't exactly work in my case so I edited it as below by guessing how it would work but failed :doh:Any chance you could help me:help??
I need to Concatenate F&G in column F if G is not blank
cHEERSSS
Sub ConcatCols()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
With .Range("F2:F" & LastRow)
If G <> "" Then
.Formula = "=F2&G2"
.Value = .Value
End With
End With
End If
End Sub
Bob Phillips
07-24-2012, 11:05 AM
Sub ConcatCols()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
End With
End Sub
Beatrix
07-25-2012, 11:26 AM
Hi xld ,
Thanks for your response. Much appreciated.
I tried to put space between 2 cell values in formula below but it wasn't a good try I guess as it didn't work. Any suggestions? Also if 2 cells are blank then cells are filled with 0. If 2 cells are empty then it should remain blank..
Last bit, I need to clear cell content in column G after merge 2 columns..Could you help me on this? Sorry for multiple requirements..
Cheers xld
Yeliz
.Formula = "=IF(H2<>"""",F2&""""&H2,F2)"
Sub ConcatCols()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
End With
End Sub
Bob Phillips
07-25-2012, 02:52 PM
What is the difference between blank and empty?
Sub ConcatCols()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&"" ""&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
.Columns("G").ClearContents
End With
End Sub
Beatrix
07-26-2012, 03:28 AM
Thanksss xld :friends:
no difference between blank and empty, just a repeat :yes My English :devil2:Blank cells were filled with 0 after applying to formula so wanted to remove them and did by editing the formula below
.Formula = "=IF(G2&H2<>"""",F2&"" ""&H2,F2)"
Cheersss xld :thumb
sindhuja
07-26-2012, 05:18 AM
Thank you so much . . . really helpful
Beatrix
08-02-2012, 05:15 AM
Hi xld ,
I need to use this script for another data scenario to concatenate 2 columns again. This time column D has some texts like "Marden House" etc and Column E has some numbers say "36" I need to merge them as "36 Marden House"
Before: D2= Marden House E2= 36
After: D2= 36 Marden House E2 should be blank.
If D2 is blank and E2=36 then nothing to merge.
I've changed cell references in below script it worked but It merged each cell value.:doh: I want to do it for nonblank cells only in column D.
Could you tell me which part I need to change exactly to make it work:help??
Sub ConcatCols2()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2&F2<>"""",F2&"" ""&D2,D2)"
.Value = .Value
End With
.Columns("F").Delete
End With
End Sub
Cheers
Yeliz
What is the difference between blank and empty?
Sub ConcatCols()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&"" ""&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
.Columns("G").ClearContents
End With
End Sub
Bob Phillips
08-02-2012, 05:35 AM
Is this what you need Yeliz?
Sub ConcatCols2()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2<>"""",E2&"" ""&D2,E2)"
.Value = .Value
End With
.Columns("E").Delete
End With
End Sub
Are you working/living in England then?
Beatrix
08-02-2012, 07:06 AM
:( it didn't work. I attached sample file to be clear on the scenario.
Is this what you need Yeliz?
Sub ConcatCols2()
'concatenate columns F & G in column F
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2<>"""",E2&"" ""&D2,E2)"
.Value = .Value
End With
.Columns("E").Delete
End With
End Sub
Are you working/living in England then?
Beatrix
08-02-2012, 07:09 AM
sorry xld , just saw your question. Yep I'm in London. You're in the UK too??
Bob Phillips
08-02-2012, 07:21 AM
I'm afraid that doesn't make anything clear to me. Your split worksheet doesn't have columns D & E, and your parseadd macro is failing when I open the workbook.
Beatrix
08-02-2012, 07:53 AM
Oops! I've run other macros on my machine and just saved the last script you sent. Please see new attachment. If column D is non-blank I need to concatenate columns E&D in columnD
I'm afraid that doesn't make anything clear to me. Your split worksheet doesn't have columns D & E, and your parseadd macro is failing when I open the workbook.
Beatrix
08-03-2012, 09:05 AM
this one is working but I couldn't manage to clear the content in column F. not entire column though. F2&"" ""&D2 after merge this two I need to clear the numbers in column F which are merged with cell values in column D.. rest of the cells in F shouldn't be cleared.
Could anyone help me on this ??
Cheers
Yeliz
Sub ConcatCols2()
'concatenate columns E & D in column D
Dim LastRow As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2<>"""",F2&"" ""&D2,"""")"
.Value = .Value
End With
.Columns("D").Delete
End With
End Sub
mancubus
08-03-2012, 02:59 PM
hi Yeliz,
if this what you want?
Sub ConcatCols()
'concatenate columns E & D in column D
Dim LastRow As Long, i As Long
With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
For i = 2 To LastRow
If .Cells(i, "D") <> "" Then
.Cells(i, "E") = .Cells(i, "F") & " " & .Cells(i, "D")
.Cells(i, "E").Font.Color = vbRed 'added for testing; may be deleted.
.Cells(i, "F").Clear
End If
Next
.Columns("D").Delete
End With
End Sub
Beatrix
08-06-2012, 03:35 AM
Exactly:cloud9:
Thanksss very much mancubus :bow:
Cheers:thumb
mancubus
08-06-2012, 04:33 AM
you're welcome.
glad it helped.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.