1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Remove Null Charectors

Discussion in 'Business Applications' started by gjmyattjr, Jan 4, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. gjmyattjr

    gjmyattjr Thread Starter

    Joined:
    Nov 30, 2010
    Messages:
    3
    I am working on a Excel problem that has me baffled. A payroll service downloads a file with names and other information. The file is saved as a .cvs file and then opened in Excel for the Payroll department to verify and load in an internal accounting system. The kicker is that the Excel version of the file .xls will not import into the accounting system because of spatial names. For example: Gonzalez Gomez, John H where Gonzolez Gomez is the last name. The last name has to exactly match the Accounting System.
    Using a Editor program I was able to determine that what looks like a space in the last name Gonzolez Gomez is actually a "NULL" (Hex 0; Ascii 0; Octal 000). While it looks like a space it isn't.

    Now I need a simple way to remove the Null and fill it with an actual SPACE (Dec 32; Hex 20; Oct 040). I don't want to have the Payroll person having to go thru a bunch of steps using 2 or 3 different programs to do this. The Payroll Clerk is currently manually looking thru the file to find those items deleating the "Space"[NULL] and then putting a real SPACE in. But as we grow to hundreds of employees this will be time consuming.

    Any one have any ideas... I can't find a solution on MS Office or any other site. I could get rid of it using my Editor but then We have to purchase a license and that may not fly with management very well.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    "Gonzalez Gomez, John H", is that all in one cell?

    What do you get using:

    =CODE(MID(ref,9,1))

    ?

    (ref being where ""Gonzalez Gomez*") is located?
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Can you not use a VBA macro and the replace function to programatically make the change? Can you post a sample CSV?

    Rollin
     
  4. gjmyattjr

    gjmyattjr Thread Starter

    Joined:
    Nov 30, 2010
    Messages:
    3
    Yes it is all in one cell. Will have to try your suggestion.
     
  5. gjmyattjr

    gjmyattjr Thread Starter

    Joined:
    Nov 30, 2010
    Messages:
    3
    not very knowledgeable in VBA. I will post a sanitized version as soon as possible
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1034530