Solved

What's hiding in this Excel cell?

Posted on 2015-01-18
5
65 Views
Last Modified: 2015-02-03
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
0
Comment
Question by:WeThotUWasAToad
  • 2
  • 2
5 Comments
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 total points
Comment Utility
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
 
LVL 22

Accepted Solution

by:
Flyster earned 300 total points
Comment Utility
If you want to move all nonprintable text, use the clean Function:

=Clean(A1)

Flyster
0
 

Author Closing Comment

by:WeThotUWasAToad
Comment Utility
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
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
=TRIM(CLEAN(A1)) . I'll have to write that one down! :)
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now