SOLVED

CONCATENATE - copy cell values ?

New Contributor

I'm having trouble trying to piece together data. I have columns which are formatted as dates and times. (Cells are set to format data as displayed below.)

|  G9   |     H9     |      I9      |    J9    |

| Day | ShortDate | StartTime | EndTime |

| Thursday | 6/16 | 6:00 PM | 8:00 PM | 

 

I'm trying to create one concatenated cell with all of the data. (This one cell would be copied over to another sheet, where I'm building a table to embed in a Word document.)

=CONCATENATE(G9&" "&H9&" from "&I9&" to "&J9) 

That "should" read "Thursday 6/16 from 6:00 PM to 8:00 PM" 

However, I'm getting this:

Thursday 44728 from 0.75 to 0.8125

 

I assume that this is an issue with the cell being formatted but the value being carried over to the concatenation..? Is there a (correct or proper) way to do this? I've tried adding VALUE(H9) and similar to the formula but that doesn't work.

Thanks in advance,

Wm

 

2 Replies
best response confirmed by MortonVisuals (New Contributor)
Solution

@MortonVisuals 

 

I'm going to take the easy way here and point you to this page https://exceljet.net/excel-functions/excel-text-function where you'll find the answer to your question. When concatenating fields that are in fact numbers, you need to convert them to text, and there's a definite syntax for that. That page will introduce you to the correct syntax for each of those numeric fields.

Thanks! I stumbled across a similar page a short while ago and realized that I didn't have my text "inserts" within the concatenate formula formatted quite right. I've got it working now, as wanted. Thank you for your time!!