What's hiding in this Excel cell?

Hello,

Can someone examine the attached Excel (2013) file and tell me what is hiding in cell A1?

I have an account at ConstantContact which I use to send business emails. Yesterday I attempted to upload ~300 new contacts but the upload file was disallowed.

By the way, each row contains a single & unique contact's data which mostly consists of typical info (eg name, phone number, email address, etc.) but also includes cells with customer comments.

Also, when the same type of issue has occurred in the past, it has always been solved by saving the file in a CSV format and then re-saving it has an Excel file before attempting the upload again. In the present case, that maneuver was not helpful.

After sending a copy of the file to ConstantContact tech support and after quite a bit of time on the phone, they were able to narrow things down a bit but not locate the specific problem.

Following that, I tried to locate unusual characters using Excel's filter tool but that too was unsuccessful. Finally, it came down to a process of simply deleting half of the rows then trying the upload and if it worked, deleting half of the remaining rows then trying the upload again. After repeating that process numerous times to identify the problem rows (the problem turned out to be present in 4 rows), the same process was required to narrow down the columns.

The bug turned out to be located in a handful of customer comments cells. In the attached file, everything has been deleted except a single problem cell.

A couple of things I noticed about the entry in this cell are shown in the following screenshots:

Clicking the formula bar and pressing Ctrl+{End} reveals the presence of invisible characters (presumably spaces) at the end of the entry (arrow points to the cursor):
Fig. 1
However, using the TRIM() function in cell A3;
Fig. 2
then copying the result and using PasteValue in A4, shows that — due to their persistent presence — the invisible characters are not simple spaces;
Fig. 3
Also, when the cell is copied and pasted into a text file, the extra spaces are removed but the entry is enclosed in quotation marks;
Fig. 4
Does that give any indication of what might be going on?

Also, for future uploads, aside from pasting the entire spreadsheet contents into Notepad and then searching for quotation marks — which not uncommonly are supposed to be present — is there any way to identify these types of problems cells?

Thanks

c-201501118-EE.xlsx
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
The contents of the cell includes two carriage return (ASCII 13) characters.
Dear Mom,
  
  I thought you might 

Open in new window

There is a space character after the last word, "might".  The Trim() function will remove this character.
0
FlysterCommented:
If you want to move all nonprintable text, use the clean Function:

=Clean(A1)

Flyster
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
I wasn't aware of the CLEAN() function but that has done the trick.

The tooltip for that function says: "Removes all nonprintable characters from text".

Initially I assumed that would include spaces so I began using CLEAN() in place of TRIM(). However, I soon discovered that beginning and trailing spaces are not removed by the CLEAN() function. Therefore, to remove every unwanted part of the entry, I've begun using =TRIM(CLEAN(A1)) and that works great.

Thanks
0
FlysterCommented:
=TRIM(CLEAN(A1)) . I'll have to write that one down! :)
0
aikimarkCommented:
I also wrote a new article on removing duplicate space characters inside strings:
Efficient String Clean up Removing Internal Duplicate Spaces: http:A_17559.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.