Formula to Remove Special Characters
TRIM will remove all char 32 spaces except for single spaces between words.
CLEAN will remove *some* nonprintable characters.
If you know what the character codes are you can use the SUBSTITUTE
function:
=SUBSTITUTE(A1,CHAR(255),"")
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(255),""),CHAR(160), "")
--
Biff
Microsoft Excel MVP
"JP Jones" wrote in message
...
I am trying to figure out a formula that I can use to remove special
characters from Excel 2007 cells. I've used a user function in the
past but can't this time. The Paste special; doesn't cut it either.
Does anyone know of a way to build a formula to remove special
characters?
Thank you,
Jeff
Here's what I've used ion the past that I can't use now in case it
might help some one.
Here's first the formula used to call the function followed by the
code.
=RegExpReplace(EndUsrPhon1,"^ +| +$|\\n|/n| |[^0-9 ]","",TRUE,FALSE)
Function RegExpReplace(LookIn As String, PatternStr As String,
Optional ReplaceWith As String = "", _
Optional ReplaceAll As Boolean = True, Optional MatchCase As
Boolean = True)
' This function uses Regular Expressions to parse a string, and
replace parts of the string
' matching the specified pattern with another string. The
optional argument ReplaceAll controls
' whether all instances of the matched string are replaced (True)
or just the first instance (False)
Static RegX As Object
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
With RegX
.Pattern = PatternStr
.Global = ReplaceAll
.IgnoreCase = Not MatchCase
End With
RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
End Function
|