Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel - Remove Null Charectors


(!)

gjmyattjr's Avatar
gjmyattjr gjmyattjr is offline
Computer Specs
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Nov 2010
Experience: Advanced
04-Jan-2012, 12:43 PM #1
Angry Excel - Remove Null Charectors
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.
bomb #21's Avatar
Member with 8,262 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Jan-2012, 03:03 PM #2
"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?
Rollin_Again's Avatar
Member with 4,686 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
04-Jan-2012, 03:29 PM #3
Can you not use a VBA macro and the replace function to programatically make the change? Can you post a sample CSV?

Rollin
gjmyattjr's Avatar
gjmyattjr gjmyattjr is offline
Computer Specs
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Nov 2010
Experience: Advanced
05-Jan-2012, 11:00 AM #4
Quote:
Originally Posted by bomb #21 View Post
"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?
Yes it is all in one cell. Will have to try your suggestion.
gjmyattjr's Avatar
gjmyattjr gjmyattjr is offline
Computer Specs
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Nov 2010
Experience: Advanced
05-Jan-2012, 11:02 AM #5
Quote:
Originally Posted by Rollin_Again View Post
Can you not use a VBA macro and the replace function to programatically make the change? Can you post a sample CSV?

Rollin
not very knowledgeable in VBA. I will post a sanitized version as soon as possible
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel, invalid charactor, null

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑