Removing unwanted characters

[Scorpvin]

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.

[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

[CLR]

There is a feature in the ASAP Utilities Add-in that will do this for
you...........it's available free at www.ASAP-utilities.com

Vaya con Dios,
Chuck, CABGx3



"Scorpvin" wrote:

>
> 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.
>
>
> --
> Scorpvin
> ------------------------------------------------------------------------
> Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
> View this thread: http://www.excelforum.com/showthread...hreadid=490221
>
>

[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

On Fri, 2 Dec 2005 11:34:09 -0600, Scorpvin
<[email protected]> wrote:

>
>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.

[Ron Rosenfeld]

On Fri, 2 Dec 2005 11:34:09 -0600, Scorpvin
<[email protected]> wrote:

>
>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.


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

[Scorpvin]

Gord,
I also need to keep the numeric characters.

[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


On Fri, 2 Dec 2005 15:18:43 -0600, Scorpvin
<[email protected]> wrote:

>
>Gord,
>I also need to keep the numeric characters.

[Scorpvin]

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

[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

On Mon, 5 Dec 2005 11:45:16 -0600, Scorpvin
<[email protected]> wrote:

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