Forum Discussion
Concatenate & #value! error
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 858-673-xxxx 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. |
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.
- I was just provided the solution, thank you.
- That worked like a charm, thank you so much. I should have tried this "community" days ago.
