PDA

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



egood
01-12-2011, 05:33 PM
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
01-12-2011, 05:45 PM
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
01-12-2011, 06:02 PM
This should work:


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

macropod
01-12-2011, 06:56 PM
Try:
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

Blade Hunter
01-12-2011, 07:08 PM
Try:
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

Nice one, I never knew about the Clean function :)

xld
01-13-2011, 01:40 AM
But how does that get rid of characters such as \ ? # etc.

macropod
01-13-2011, 04:36 AM
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!

xld
01-13-2011, 05:06 AM
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
01-13-2011, 02:37 PM
Mine is not to reason why ...