VBA help to add numbers after a word in a column

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,072
Office Version
  1. 365
Platform
  1. Windows
Below is just showing an example on what I need to do

I need col D & E to look like H & I results

I just need column D's numbers to go after Column E's word one space and then the number.

Any help thank you




Excel 2016 (Windows) 32 bit
C
D
E
F
G
H
I
1
orignalUnion LocalUnion NameResultsUnion LocalUnion Name
2
20​
Local
20​
Local 20
3
20​
Local
20​
Local 20
4
6,18A,20Local6,18A,20Local 6,18A,20
5
20​
Local
20​
Local 20
6
20​
Local
20​
Local 20
Sheet: Sheet1
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Where do U want the results? As displayed in H & I? Seems like this should work. Dave
Code:
Sub test()
Dim Cnt As Integer, Lastrow As Integer
With Sheets("Sheet1")
    Lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
Sheets("sheet1").Cells(Cnt, "H") = Sheets("sheet1").Cells(Cnt, "D")
Sheets("sheet1").Cells(Cnt, "I") = Sheets("sheet1").Cells(Cnt, "E") _
                        & " " & Sheets("sheet1").Cells(Cnt, "D")
End Sub
 
Upvote 0
Where do U want the results? As displayed in H & I? Seems like this should work. Dave
Code:
Sub test()
Dim Cnt As Integer, Lastrow As Integer
With Sheets("Sheet1")
    Lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
Sheets("sheet1").Cells(Cnt, "H") = Sheets("sheet1").Cells(Cnt, "D")
Sheets("sheet1").Cells(Cnt, "I") = Sheets("sheet1").Cells(Cnt, "E") _
                        & " " & Sheets("sheet1").Cells(Cnt, "D")
End Sub

Results in D and E. I just put it in H and I to show the results on what it looked like
 
Upvote 0
This should do it. Dave
Code:
Sub test()
Dim Cnt As Integer, Lastrow As Integer
With Sheets("Sheet1")
    Lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
Sheets("sheet1").Cells(Cnt, "E") = Sheets("sheet1").Cells(Cnt, "D") _
                        & " " & Sheets("sheet1").Cells(Cnt, "E")
End Sub
 
Upvote 0
Another option, would be
Code:
Sub Concat()

   With Sheets("Sheet1")
      With .Range("E2", .Range("E" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace(Replace("if(@1<>"""",@ & "" "" & @1,@)", "@1", .Offset(, -1).Address), "@", .Address))
      End With
   End With
      
End Sub
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top