Solved: Help with replace [a-z][A-Z][0-9]

[egood]

After searching through the forum I couldn't find any leads for what I want to do. I figure there's a way to do this without going to REGEX.

I would like to replace the non-alphabetical and non-numerical characters (those other than [a-z][A-Z][0-9]) with "" for the contents of cell A1.

Can anyone point me to a previous post or provide some code to get me going? I am using Excel 2007.

Thanks in advance.

[stanleydgrom]

egood,

Welcome to the VBA Express forum.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


Have a great day,
Stan

[Blade Hunter]

This should work:

[vba]
Sub RemoveExtendedChars()
For X = 1 To 47
Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
Next
For X = 58 To 64
Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
Next
For X = 91 To 255
Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
Next
End Sub
[/vba]

[macropod]

Try:
[VBA]Sub RemoveExtendedChars()
Dim oCel As Range
'In the Unicode character set, there are additional nonprinting
'characters (values 127, 129, 141, 143, 144, and 157). By itself,
'the Clean function does not remove these additional nonprinting characters.
For Each oCel In Selection
oCel.Formula = WorksheetFunction.Clean(oCel.Formula)
Next
End Sub[/VBA]

[Blade Hunter]

Try:
[vba]Sub RemoveExtendedChars()
Dim oCel As Range
'In the Unicode character set, there are additional nonprinting
'characters (values 127, 129, 141, 143, 144, and 157). By itself,
'the Clean function does not remove these additional nonprinting characters.
For Each oCel In Selection
oCel.Formula = WorksheetFunction.Clean(oCel.Formula)
Next
End Sub[/vba]

Nice one, I never knew about the Clean function

[Bob Phillips]

But how does that get rid of characters such as \ ? # etc.

[macropod]

Hi xld,

It wouldn't - but is that actually what the OP actually wants to do? I'll grant that the problem description suggests so, though, and that even spaces, commas & periods would be deleted. Deleting periods could have intersting effects with decimal values!

[Bob Phillips]

Exactly, he said ... I would like to replace the non-alphabetical and non-numerical characters (those other than [a-z][A-Z][0-9]) ..., so why he is happy with something that doesn't do that?

[macropod]

Mine is not to reason why ...