PDA

View Full Version : Solved: Blanks and bad characters



jwise
01-20-2009, 08:54 AM
I have a worksheet that is actually a file description. I can turn this worksheet into a program to read the file (outside of Excel). However, some of the cells contain blanks or other undesirable characters (as defined by the language). For example:

ZIP+4
ABCD EFGH IJKL

I want to translate "ZIP+4" to "ZIP4", and "ABCD EFGH IJKL" to "ABCDEFGHIJKL". I found plenty of routines to eliminate blank lines, but no examples of how to eliminate a blank or other character embedded in a cell.

Any ideas? TIA

nst1107
01-20-2009, 09:26 AM
You can use the Replace() function to replace all instances of the undesired characters and blanks with vbNullString. Something like:Option Explicit
Sub RemoveBadCharacters()
Dim ws As Worksheet
Dim c As Range
For Each ws In ThisWorkbook.Worksheets
For Each c In ws.UsedRange
c = Replace(c, " ", vbNullString)
c = Replace(c, "+", vbNullString)
DoEvents
Next
Next
End Sub
Seems like this might not be the best way, though. Probably will wind up taking a long time to run the code if you have a lot of used cells on each worksheet. Someone else may have a better idea.

Bob Phillips
01-20-2009, 09:29 AM
Replace them

=SUBSTITUTE(SUBSTITUTE(A2,"+","")," ","")

jwise
01-20-2009, 10:24 AM
Thanks for the suggestion. When Help failed for "translate", I didn't think of alternative names the function could be called. It seems I need a printed reference because you discover a lot of this stuff looking for other things.

This will be used on other projects, and this one is 340+ rows. "Replace" seemed instantaneous, but I'm going to try "Substitute" also. Thanks again to both responders.