Retaining formatting in concatenate

[RussellNonBrand]

Hey everyone,
I have a weird situation. There is a chart, then I write a sentence below it for each week, summarizing data points. It's not hard, but very time consuming, seeing as how I have usually 10-15 charts/week and two summaries per chart. So I wrote out some code to concatenate text and data, but it won't retain formatting. Date values come through as the Excel Date Value, percentages come through as decimals, and large numbers do not include commas. Is there a way to do this?

I have attached an example, as well as some really ugly code I wrote to manually format it. Is there a simple command to just format Number with comma, Percent, etc?

Thanks!

[daddylonglegs]

You can use a mixture of TEXT function (to dictate the number format for dates and/or percentages) or FIXED function which fixes decimal places for a number (and by default shows comma separators), e.g. try

=CONCATENATE("> The number of opened emails for ",TEXT(B7,"m/d")," was ",FIXED(J7,0)," with an open rate of ",TEXT(K7,"0.00%")," which is ",IF(K7>K8,"higher","lower")," than the average open rate from the past ",COUNT(C2:C7)," weeks.")

[RussellNonBrand]

That is so much prettier than the cobbled together mess of a code I had written, haha. Thank you!