Losing formatting using VBA code for concat

[Cherhope]

Hi everyone. I'm new to using macros so would appreciate some help. I want to join multiple columns of data and retain the original formatting. Some columns contain text, some text and numbers and one column has the time. The VBA code I'm using joins the columns appropriately but the time is converted to decimals. i've tried all the different Paste Special commands (xl PasteValues, xlPasteValuesandNumberFormats etc.) but nothing seems to work.

Here is the VBA code:
Sub SpecialConcat()
Dim lastRow As Long
Dim wsData As Worksheet

'Where is the data?
Set wsData = Worksheets("RSG New Download")

Application.ScreenUpdating = False
With wsData
'Find where our data ends
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Range("J1").EntireColumn.Insert
With .Range("J2:J" & lastRow)
.Formula = "=F2 & "" "" & G2 & "" "" & H2 & "" "" & I2 & "" "" & ""EDT"""
.Copy
.PasteSpecial xlPasteValuesAndNumberFormats
End With
.Range("F:I").Delete
End With

Application.ScreenUpdating = True

End Sub

This is the original data:

HMB430H1F	LEC0101	Wednesday	6:00 PM​
CSB343	Lec0101	Saturday	1:00 PM​
ENG273Y1 Y	LEC5101	Wednesday	4:00 PM​

I want to join the 4 columns so the entries looks like: HMB430H1F LEC0101 Wednesday 6:00 PM EDT

This is the result I get when I run the code:

HMB430H1F LEC0101 Wednesday 0.75 EDT

CSB343 Lec0101 Saturday 0.541666666666667 EDT

ENG273Y1 Y LEC5101 Wednesday 0.666666666666667 EDT

Thanks!

Cheryl

 

[Joe4]

Welcome to the Board!

Concatenation does NOT maintain formatting, as it is only concatenating the underlying values, not any formatting applied to the cell.
If you want the formatting, you need to use the TEXT function (for native Excel formulas) or corresponding FORMAT function (in VBA).

See here for help on the TEXT function, for dates and times: Excel formula: Convert date to text | Exceljet
And see here for help on the FORMAT function (which works similarly, but just for VBA): VBA FORMAT Function

So, you would apply the appropriate TEXT/FORMAT function to just the one piece of the concatenation (and not the whole thing), like shown here:

Combine text with a date or time - Microsoft Support

Suppose you want to create a grammatically correct sentence from several columns of data for a mass mailing or format dates with text without affecting formulas that use those dates. To combine text with a date or time, use the TEXT function and the & (ampersand) operator.

support.microsoft.com

 

[Alex Blakenburg]

Taking Joe's adviced 1 step further, try this:

.Formula = "=F2 & "" "" & G2 & "" "" & H2 & "" "" & Text(I2, ""hh:mm AM/PM"") & "" "" & ""EDT"""

 

[Cherhope]

Welcome to the Board!

Concatenation does NOT maintain formatting, as it is only concatenating the underlying values, not any formatting applied to the cell.
If you want the formatting, you need to use the TEXT function (for native Excel formulas) or corresponding FORMAT function (in VBA).

See here for help on the TEXT function, for dates and times: Excel formula: Convert date to text | Exceljet
And see here for help on the FORMAT function (which works similarly, but just for VBA): VBA FORMAT Function

So, you would apply the appropriate TEXT/FORMAT function to just the one piece of the concatenation (and not the whole thing), like shown here:

Combine text with a date or time - Microsoft Support

Suppose you want to create a grammatically correct sentence from several columns of data for a mass mailing or format dates with text without affecting formulas that use those dates. To combine text with a date or time, use the TEXT function and the & (ampersand) operator.

support.microsoft.com

Welcome to the Board!

Concatenation does NOT maintain formatting, as it is only concatenating the underlying values, not any formatting applied to the cell.
If you want the formatting, you need to use the TEXT function (for native Excel formulas) or corresponding FORMAT function (in VBA).

See here for help on the TEXT function, for dates and times: Excel formula: Convert date to text | Exceljet
And see here for help on the FORMAT function (which works similarly, but just for VBA): VBA FORMAT Function

So, you would apply the appropriate TEXT/FORMAT function to just the one piece of the concatenation (and not the whole thing), like shown here:

Combine text with a date or time - Microsoft Support

Suppose you want to create a grammatically correct sentence from several columns of data for a mass mailing or format dates with text without affecting formulas that use those dates. To combine text with a date or time, use the TEXT function and the & (ampersand) operator.

support.microsoft.com

Thank-you Joe for replying to my post, for the resources and for letting me know that concatenation doesn't retain formatting!

Have a good day!

 

[Cherhope]

Taking Joe's adviced 1 step further, try this:

.Formula = "=F2 & "" "" & G2 & "" "" & H2 & "" "" & Text(I2, ""hh:mm AM/PM"") & "" "" & ""EDT"""

Thanks so much Alex! It worked!!!!! This will really help my team to place students into their study groups much faster.

Have a great day!

Cheryl

 

[Alex Blakenburg]

Glad we could help & thanks for the feedback.

 

[Joe4]

Thank-you Joe for replying to my post, for the resources and for letting me know that concatenation doesn't retain formatting!

Did you take a look at the links I provided (there was a lot more to my reply than that).
The links basically explain and show you how to do what Alex posted.

 

[Cherhope]

Did you take a look at the links I provided (there was a lot more to my reply than that).
The links basically explain and show you how to do what Alex posted.

Hi Joe. I apologize for not acknowledging that there was much more to your reply than what I acknowledged in my first reply to your post. I read through the links your provided multiple times but I have such a rudimentary knowledge of macros and coding that I couldn't write the code (I tried for several hours last night because I really wanted to learn how to do it). I tried several different versions of coding in several different positions within the VBA code that I already had but I couldn't figure it out. I didn't think to place it within the formula line (I kept placing it in it's own line). With your links explaining & showing and Alex's line of code, I know see what you meant by applying the formatting to only the one piece of the concatenate. It all makes sense know but my brain last night just couldn't get it even though you provided me with what I needed.

 

[Joe4]

Hi Joe. I apologize for not acknowledging that there was much more to your reply than what I acknowledged in my first reply to your post. I read through the links your provided multiple times but I have such a rudimentary knowledge of macros and coding that I couldn't write the code (I tried for several hours last night because I really wanted to learn how to do it). I tried several different versions of coding in several different positions within the VBA code that I already had but I couldn't figure it out. I didn't think to place it within the formula line (I kept placing it in it's own line). With your links explaining & showing and Alex's line of code, I know see what you meant by applying the formatting to only the one piece of the concatenate. It all makes sense know but my brain last night just couldn't get it even though you provided me with what I needed.

Gotcha.

Note that if you get "stuck", simply post back letting us know that you are having trouble (and post what you have tried), and we can help you work through it.
We appreciate that you make the effort, but know that sometimes a little more assistance may be required. But we don't know if you still need additional help unless you let us know.