remove hidden characters in Excel

dbwiz

Active Member
Joined
Nov 20, 2007
Messages
275
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.
 
The Clean Function worked for me where I was finding a hidden space inside Hyperlinks for a Tab Delimited Spreadsheet uploading to Google Merchant Centre. Thanks, you saved me about 6 hours work re-writing - I was at my wits end!
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Unicode has defined a few characters that may appear as commas, depending on the font used to display the glyphs.

Some accent characters can appear to be commas when using some fonts. A stand-alone cedilla, as on the c in façade, may look like a comma. There are also some other glyphs that might be mistaken for commas, but I forget most of them: they are rare for an American to see.

Unicode also has invisible characters. U+2063, an invisible separator, can be used to prevent this forum's software translating a word to BBS code or inserting smilies where you don't want them. Here's an example line where it's used to prevent the interpretation of code tags.

[⁣code]These are not code tags.[⁣/code]

I wonder what the original encoding was. A code editor with more capabilities than Notepad should be able to determine what is the mystery character and be able to search for it and delete it. I hesitate to recommend that, because most code editors I'm familiar with have a steep learning curve.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,214,429
Messages
6,119,424
Members
448,896
Latest member
MadMarty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top