Solved

What's hiding in this Excel cell?

Posted on 2015-01-18
5
66 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
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.
0
 
LVL 22

Accepted Solution

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

=Clean(A1)

Flyster
0
 

Author Closing Comment

by:WeThotUWasAToad
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.

Thanks
0
 
LVL 22

Expert Comment

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

Expert Comment

by:aikimark
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

947 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

21 Experts available now in Live!

Get 1:1 Help Now