Solved

What's hiding in this Excel cell?

Posted on 2015-01-18
5
67 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA User Form Help 21 28
Select/Copy row and pasting it lower in sheet 7 19
Assigning VBA code to a specific worksheet 3 24
sort column using vba 2 16
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

776 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