Page 1 of 2 12 LastLast
Results 1 to 10 of 13

remove hidden characters in Excel

This is a discussion on remove hidden characters in Excel within the Excel Questions forums, part of the Question Forums category; We received a .xls Excel spreadsheet which we need to convert to .csv to submit to the government. However when ...

  1. #1
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default remove hidden characters in Excel

    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.

  2. #2
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,555

    Default Re: remove hidden characters in Excel

    Silly Q: Are we sure they are not the commas that define the 'c' in 'csv'?
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  3. #3
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default Re: remove hidden characters in Excel

    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.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,156

    Default Re: remove hidden characters in Excel

    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).

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: remove hidden characters in Excel

    You can also try the =CLEAN() function, which removes non-printable characters from a text string.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  6. #6
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default Re: remove hidden characters in Excel

    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?

  7. #7
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default Re: remove hidden characters in Excel

    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?

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Posts
    117

    Default Re: remove hidden characters in Excel

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

  9. #9
    Board Regular dbwiz's Avatar
    Join Date
    Nov 2007
    Location
    California
    Posts
    275

    Default Re: remove hidden characters in Excel

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

  10. #10
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,156

    Default Re: remove hidden characters in Excel

    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.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com