Excel 2010 -- Non-display characters

General Questions
#1

I’ve recently upgraded to Excel 2010 at the office, and it’s taking some getting used to. In Excel 2003 I could see NDCs if they were present in the data when I turned wrapping off. They appeared as little boxes. This doesn’t seem to work in Excel 2010.

  1. Is there a way I can see NDCs such as ALT-010?

We received a .txt file that has five to seven records per account (account number, business name, secondary name, etc.), which needs to be reformatted such that each account comprises a single record. The .txt file is imported into Excel and rearranged. This particular file contains tabs.

  1. Is there a way to make tabs visible in Excel?

  2. What is the ALT code for them, so that I can change them all to spaces in Notepad?

#2

Just thought I’d ask again. I have a file that has manual line breaks in a few cells. They are ALT-010, so there’s no problem if I’m in the office. But I can’t find them and delete them by using the ALT code when I’m telecommuting on my Mac. It’s no problem just going to the cells and deleting the part of the information I don’t need (i.e., after the line break) and the line break. But it would be nice if I could display them so I could see them, even if I’m on the PC.

How can I show them in Excel 2010?

#3

I think the tab is ALT-009.

There must be a setting somewhere, because mine shows special characters. It actually shows the characters, not the little boxes.

I haven’t found it yet, though. I did find this, if that helps any.

#4

Also, this may not work because I’m not entirely clear on what you’re deleting, but…

have you ever looked at the CLEAN function?

#5

Re: The link. I’m loathe to install any programs on the company’s computer.

Most of our data arrives as Excel files. It needs to be fixed-format text in order to run through my programs. Excel won’t save files as fixed-format text in the way they need to be saved, so I get rid of double-quotes, commas, and any non-display characters and save it as a .csv file. This file is imported into Access and then exported as text, defining the field lengths and starting positions. Commas and non-display characters cause fields to be shifted when saved as a .csv file (which kind of defeats the purpose of making a fixed-position file! :stuck_out_tongue: )

In an Excel file, with wrapping turned on, the non-display characters result in two lines being displayed in a cell; like this:

123 FAKE ST
ADMIN BUILDING

With wrapping turned off, it looks like this:

123 FAKE STADMIN BUILING

What I do is delete the non-display character and what follow it, so that I only have one line per cell: 123 FAKE ST. If there are a great many non-display characters in the file, I can F&R ALT-010 with a pipe. (This is useful when I actually want what follows the break.) Since I can’t use ALT codes from my Mac, I use NoOneSpecial’s technique of copying the Excel file into Word and using the drop-down menu to accomplish the same. Obviously that’s ridiculous if I only need to edit two cells.

Usually wrapping is turned off, or else I turn it off. So seeing which cells might have NDCs in them is difficult (see the previous example). When I had Excel 2003, the NDCs would show up as little squares, similar to this: – except they’re not square brackets but actual little squares. This made them easy to see:

123 FAKE STADMIN BUILDING

So what I want to find out, is if there is any way to make the NDCs visible as above, and as they were in Excel 2003. Getting rid of them isn’t a problem; it would just be convenient if I could see them.

#6

Thought that’s what you were saying.

That’s why I hate working with Excel for this stuff; give me straight text files baby!

I haven’t been able to find anything. Maybe you should try one of the Excel forums?’