Removing unwanted characters

S

Scorpvin

Column A has characters such as - ' . @ # ? " ( ) % ^ & + How do
remove all types of characters in this column? The only thing I wan
is the text
 
D

Dnereb

You could use a VBa routine with some replace statments
in a loop through al the cells alter the content for a specific
character to an empty string:

Cells(X,Y) = Replace(Cells(X,Y),"~","")
Cells(X,Y) = Replace(Cells(X,Y),"^,"")
etc...

Questions...... mail me
 
G

Gord Dibben

Public Sub StripAllButAZs()
''strips out everything except letters
Dim myRange As Range
Dim Cell As Range
Dim myStr As String
Dim i As Integer

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each Cell In myRange
myStr = Cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 90) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
Cell.Value = Application.Trim(myStr)
Next Cell
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben Excel MVP
 
R

Ron Rosenfeld

Column A has characters such as - ' . @ # ? " ( ) % ^ & + How do I
remove all types of characters in this column? The only thing I want
is the text.
Click to expand...


1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

=REGEX.SUBSTITUTE(A1,"[^0-9A-z]")

This formula says to replace everything that is not a digit or a letter with
"nothing".

It is the equivalent of:

=REGEX.SUBSTITUTE(A1,"[^\d\w]")


--ron
 
G

Gord Dibben

Your origianl post asked for all but text to be removed.

Here's one that keeps text and numbers.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord
 
S

Scorpvin

Gord,
I'm not very familiar with VB. How do I get a space to replace th
unwanted character in your statement
 
G

Gord Dibben

Try this routine

Public Sub Strip_Pick()
Dim myRange As Range
Dim Cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each Cell In myRange
myStr = Cell.Text
For i = 1 To Len(myStr)
If Not (Mid(myStr, i, 1)) Like "[0-9a-zA-Z]" Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
Cell.Value = Application.Trim(myStr)
Next Cell
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End If
End Sub


Gord
 
Top