Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php...o&userid=26182 View this thread: http://www.excelforum.com/showthread...hreadid=490221 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
On Fri, 2 Dec 2005 11:34:09 -0600, 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. 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
Gord, I also need to keep the numeric characters. -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=490221 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 wrote: Gord, I also need to keep the numeric characters. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
Gord, I'm not very familiar with VB. How do I get a space to replace the unwanted character in your statement? -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=490221 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing unwanted characters
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 wrote: Gord, I'm not very familiar with VB. How do I get a space to replace the unwanted character in your statement? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing pre-set characters from comments | Excel Worksheet Functions | |||
Removing Non-Numeric Characters | Excel Discussion (Misc queries) | |||
Removing text characters | Excel Worksheet Functions | |||
Removing blank characters ? | Excel Discussion (Misc queries) | |||
removing some of the characters from a cell | Excel Discussion (Misc queries) |