Removing characters from a text string
 

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

--
Message posted from http://www.ExcelForum.com

Removing characters from a text string
 

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

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE
(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

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


---
Message posted from http://www.ExcelForum.com/

.

Removing characters from a text string
 

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




"zaisaki " wrote in
message ...
Hi,
How do I remove characters (.-/* etc) from a text string in
excel.
Thanks,
Zai Saki


---
Message posted from http://www.ExcelForum.com/

Removing characters from a text string
 

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

On Sat, 28 Aug 2004 09:22:33 -0500, zaisaki
wrote:

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


---
Message posted from http://www.ExcelForum.com/