Solved: Blanks and bad characters

[jwise]

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]

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]

Replace them

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

[jwise]

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.