Since you have multiple formats in the first column, you have to change the format in the TEXT function each time.
This is really painful!
To my knowledge, there is no way in native Excel to extract the format of the cell and pass it to the TEXT function, so you have to change it manually each time.
But always there is a solution in VBA.
I've written the custom function below in VBA, which can do this for you:
Public Function ConcatKeepFormat(Value1 As Range, Value2 As Range) As String Dim cellFormat As String cellFormat = Value1.NumberFormat ConcatKeepFormat = Format(Value1, cellFormat) & Value2 End Function
Please find the attached workbook to test it.
To locate the code, press Alt+F11, and find it in the Module1 of the current workbook project.