Concatenate & #value! error

Copper Contributor

I am using the following function =CONCATENATE(TEXT(I83,"m/d/yy h:mm")," ",TEXT(J83,"text")) to combine the contents of two cells. The problem is that the second cell J83 is formatted as General and when reviewing this format, the sample shows #############...############  This is causing the result to show a #VALUE! error. Some of my cells in the J column are formatted as General but the sample shows the text that is actually in the cell and for some reason, other cells in this column that I am trying to Concatenate have the General format but reflect the sample as the # signs. Even if I change the format in J to Text, it still results in an error. I can't seem to change the format for something else that will work. When I review the content in the J column cell, the ones that seem to be causing the problem do not have any "tabs" between the data content, and the J column cells that work, appear to have a large space between text and are arranged on separate lines. Here is a sample of the J cell content that is formatted correct with the General format:


08/01/2010 11:31AM by KL Vernon - He sent me an e-mail asking about lease comps on the small office space I leased to Steve Daniels.

xxx W Bernardo Ct, Suite 200
San Diego, CA 92127-1639
FAX: 858-673-xxxx

 The General format that seems to produce the error is below: 

10/25/2010 12:04PM by Kevin Vernon - She is a recruit of xxxx who has not yet joined xx. She called me this morning with a referral and we agreed that I would help her find 2-5 acres in Menifee for a 25% fee to me and she handles the deal. I am sending her some listings from both LoopNet and MLS.
4 Replies


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

best response confirmed by Hans Vogelaar (MVP)


Why not?

=CONCATENATE(TEXT(I83,"m/d/yy h:mm")," ",J83)
That worked like a charm, thank you so much. I should have tried this "community" days ago.
I was just provided the solution, thank you.