Formula to Remove Special Characters
 

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

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

Formula to Remove Special Characters
 

On Tuesday, December 8, 2009 at 7:56:06 AM UTC+5:30, JP Jones wrote:
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

Hi Team,

please help me with the formula to remove the special characters from the strings like below

Vim Drop DwÃÆÀ*€„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆ €šÃ€šÃ€šÃ‚ Gel-Lmon, 115ml Pack