View Full Version : [SOLVED:] Concatenate cells with leading zeros vba
podder
01-06-2018, 01:24 AM
Hi,
Can anybody help, I require 5 cells in a row to be joined together as one, however, one cell has a leading zero (which must remain) and each cell must be separated by a space.
I have imported the data and cant change the cells to text (for leading zeros), as for this to work I must physically enter the zeros.
Does anyone have an idea how this can be achieved using vba
Maybe...
Sub test()
Dim Lastrow As Integer, Rng As Range, R As Range
Dim STR As String, Oe As String
With Sheets("sheet1")
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set Rng = Sheets("Sheet1").Range("A1:A" & Lastrow)
For Each R In Rng
STR = STR & R.Text & " "
Next R
STR = Left(STR, Len(STR) - 1)
MsgBox STR
End Sub
HTH. Dave
podder
01-06-2018, 03:34 PM
Maybe...
Sub test()
Dim Lastrow As Integer, Rng As Range, R As Range
Dim STR As String, Oe As String
With Sheets("sheet1")
Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set Rng = Sheets("Sheet1").Range("A1:A" & Lastrow)
For Each R In Rng
STR = STR & R.Text & " "
Next R
STR = Left(STR, Len(STR) - 1)
MsgBox STR
End Sub
HTH. Dave
Thanks for the reply Dave,
The code you sent me only shows the output in a window and does not retain it in a cell.
Also, each row needs to be in a separate cell
Thanks
Yes it only produces an output msgbox to see if the output is correct. U need to test the code and see if it correctly outputs what U want. Care to share where and how U want to place your output? Dave
podder
01-06-2018, 09:56 PM
Yes it only produces an output msgbox to see if the output is correct. U need to test the code and see if it correctly outputs what U want. Care to share where and how U want to place your output? Dave
Yes, Sorry Dave
I want the output to be placed in column A - the output msgbox is correct - Thanks
Need a bit more info.... where is the input data located (rows and column) and what row(s) do you want the output? Dave
podder
01-07-2018, 04:31 PM
Need a bit more info.... where is the input data located (rows and column) and what row(s) do you want the output? Dave
Columns are B to E and rows are to the last row of data located in column B are what need to be concatenated and the output in Column A
Hope this is enough information
Also, would it be possible to miss a column using the same idea - as in Concatenate columns B, C, D, and F and input into Column A
So U actually want to concatenate non-contiguous columns not " I require 5 cells in a row to be joined together as one". This is fairly basic. Dave
Sub test()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("sheet1")
Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
Sheets("Sheet1").Cells(Cnt, "A") = CStr(Sheets("Sheet1").Cells(Cnt, "B")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "C")) & " " & CStr(Sheets("Sheet1").Cells(Cnt, "D")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "F"))
Next Cnt
End Sub
podder
01-10-2018, 04:38 AM
So U actually want to concatenate non-contiguous columns not " I require 5 cells in a row to be joined together as one". This is fairly basic. Dave
Sub test()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("sheet1")
Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
Sheets("Sheet1").Cells(Cnt, "A") = CStr(Sheets("Sheet1").Cells(Cnt, "B")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "C")) & " " & CStr(Sheets("Sheet1").Cells(Cnt, "D")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "F"))
Next Cnt
End Sub
Dave it works great
Thanks for your help just what I needed
You are welcome. Thanks for posting your outcome. Dave
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.