Removing characters from a text string

[zaisaki]

Hi,
How do I remove characters (.-/* etc) from a text string in excel.
Thanks,
Zai Sak

 

[Jason Morin]

If you want to remove 8 different types of characters or
less, you could use SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-
",""),"*",""),"/",""),"?",""),"@",""),"^",""),".",""),"!","
")

You could continue this with defined names, but if you
really need to remove more than 8 types, it's best to use
a VBA routine.

HTH
Jason
Atlanta, GA

 

[Chip Pearson]

Try something like the following:

Const CHARS_TO_REMOVE = "/\-*" 'add additional chars
Dim Ndx As Integer
Dim S As String
S = "Some / Text * String\"
For Ndx = 1 To Len(CHARS_TO_REMOVE)
S = Replace(S, Mid(CHARS_TO_REMOVE, Ndx, 1), "")
Next Ndx
Debug.Print S



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Gord Dibben]

Zai

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 Dibben Excel MVP