Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Delete numbers and special characters

Delete numbers and special characters

I have a database that I need to clean up and I have to get rid of some characters and other stuff in it.  I have to do it in vbscript so I need a little help.  I can query and return the data fine.  

Here is the problem, I have some entries that are supposed to be last names but because of laziness people have entered some gibberish here is an example.


is there a way to tell that a character is not alpha and how do I remove them?


-How important does a person have to be before they are considered assassinated instead of just murdered?
-Need more cow bell!!!

RE: Delete numbers and special characters

One very low-tech (and slow) solution would be passing each string into this function:


Dim s
s = "Goldberg(#44345)"
s = FixName(s)
wscript.echo s

Function FixName(s)
   Dim ch
   Dim sAcceptableChars
   sAcceptableChars = "abcdefghijklmnopqrstuvwxyz"

   For x = 1 to Len(s)
      ch = mid(s, x, 1)
      If InStr(sAcceptableChars, LCase(ch)) > 0 Then
         FixName = FixName & ch
      End If

End Function

Just add any other characters you want to sAcceptableChars (like a space, or apostrophe maybe).

HOWEVER, this would be much more efficiently solved with Regular Expressions, I'm just not good enough with them to provide an example.

RE: Delete numbers and special characters

Simple RegEx


Option Explicit

Dim arrTemp : arrTemp = Array("NewMan001", "Goldberg(#44345)")
Dim RegEx : Set RegEx = New RegExp
RegEx.Pattern = "[^A-Z]+"
RegEx.IgnoreCase = True
Dim strTemp
For Each strTemp In arrTemp
    WScript.Echo RegEx.Replace(strTemp, "")

My philosophy: K.I.S.S - Keep It Simple Stupid

RE: Delete numbers and special characters

OK, I have been using this but I have one problem I have some apostrophes ' withing the name, like o'brien, o'tool.  I can I keep the apostrophes in?


-How important does a person have to be before they are considered assassinated instead of just murdered?
-Need more cow bell!!!

RE: Delete numbers and special characters

Just change the pattern

RegEx.Pattern = "[^A-Z']+"

"... isn't sanity really just a one trick pony anyway?!  I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close