I have a problem with combining the contents of three columns into one for a description of a security to be used with Bloomberg's Excel API. The Excel date format is getting in the way as it doesn't store the value the way it visually appears. I have a column for ticker, one for coupon and one for maturity, formatted as date. In the example below, I have two maturity columns, formatted as text & date respectively for illustrative purposes. I have tried a combination of LEFT,MID,RIGHT, concatenations, ampersands, adding characters to the maturity, changing format, but nothing I have tried has worked getting around the Excel date format, as can be seen in the rightmost column.
Please see the column second from the right for an example of the desired result. If you have a formula or a macro that can do this, I would be very grateful.
Ticker Coupon maturity maturity Desired Result Actual Result
ABC 4.000 44532 12/2/2021 ABC 4 12/2/2021 ABC 4 44532
ABC 2.250 43432 11/28/2018 ABC 2.25 11/28/2018 ABC 2.25 43432
Thank you.
If the other values in the last column are strings, you can concatenate the maturity values that are dates with the text() formula, I believe.
Taking maturity as the third column (so you can omit the 4th column that converts the Long date value to a formatted date):
=A2&" "&B2&" "&TEXT(C2, "mm/dd/yyyy")
Don't have excel at home, but that should do it.