What's hiding in this Excel cell?

Posted on 2015-01-18
Medium Priority
Last Modified: 2015-02-03

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?


Question by:WeThotUWasAToad
  • 2
  • 2
LVL 47

Assisted Solution

aikimark earned 800 total points
ID: 40556953
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.
LVL 22

Accepted Solution

Flyster earned 1200 total points
ID: 40556964
If you want to move all nonprintable text, use the clean Function:



Author Closing Comment

ID: 40587653
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.

LVL 22

Expert Comment

ID: 40587756
=TRIM(CLEAN(A1)) . I'll have to write that one down! :)
LVL 47

Expert Comment

ID: 40587794
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question