Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing first 10 characters of text | Excel Worksheet Functions | |||
removing text characters from a cell | Excel Worksheet Functions | |||
Please help with removing text string | Excel Discussion (Misc queries) | |||
Removing non text characters from spreadsheet | Excel Discussion (Misc queries) | |||
Removing text characters | Excel Worksheet Functions |