VBA help to add numbers after a word in a column

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,986
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,911
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
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,986
Office Version
  1. 365
Platform
  1. Windows
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
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,911
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,005
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
1,986
Office Version
  1. 365
Platform
  1. Windows
Thanks guys for all the help cheers :biggrin:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,005
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,148,291
Messages
5,745,905
Members
423,983
Latest member
blackworx

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
Top