remove hidden characters in Excel

[dbwiz]

We received a .xls Excel spreadsheet which we need to convert to .csv to submit to the government. However when looking at the .csv file in Notepad, there are hidden commas in a lot of cells, but they appear to be blank in both the original file and the converted file.

How can I remove these hidden characters? It is a huge spreadsheet.

 

[tweedle]

Silly Q: Are we sure they are not the commas that define the 'c' in 'csv'?

 

[dbwiz]

I should think that if that were the case, then they were be consistent throughout. But it seems to be sporadic. For example, the first ones show up in AF35 to AO35 and then none until say row 48. I also do not know why if certain cells which appear to be blank in the original spreadsheet would suddenly get a comma that still appear to be blank in the .csv spreadsheet but actually have a comma in it (as well as the commas which defined the breaks).

If anyone has any thoughts at all on this (or if there is some VB I could run to SEE those hidden commas) that would be helpful. Having to look at .csv in notepad and edit it manually is painful.

 

[xenou]

Some non-printing characters exist but aren't visible. This happens frequently with data from the web. Not much to do about it - the characters exist even if they aren't visible, so that's that. If they are corrupting your data you have to clean them out which can be tricky. If you can identify the character, you may be able to use a simple find/replace, either in Excel or in Notepad (sometimes you can do this by copying the character to put in the "find" field, even if you don't know what it is).

 

[Gerald Higgins]

You can also try the =CLEAN() function, which removes non-printable characters from a text string.

 

[dbwiz]

I know what the character is - it is a comma. Problem is within the Excel .csv file, I cannot find any commas to replace. But if I were to find/replace commas within the notepad .csv file - that would take out the delimiter as well as the ones that are contained within those cells...

Thoughts?

 

[dbwiz]

Doesn't the =CLEAN() function have to be done from a different cell? (say you want to clean A1) in case there is something in it you do not see. Would I not have to insert a column in front of B (for example) and put the formula in B1?

 

[cb12]

How about saving it as tab delimited and then taking out the commas?

 

[dbwiz]

Good idea (thinking outside the proverbial box!) I'll try it.

 

[xenou]

I rather doubt it's a comma. These would be visible in Excel and would be quoted when saved as CSV ... unless they are blank cells ... (?)

It *will* probably help to sequester them using a tab-delimiter.