×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

*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.

# delete only letters from a field with numbers and letters3

## delete only letters from a field with numbers and letters

(OP)
I have a field that contains data similar to this:
4
F3
8A
2-6
2FL
I need to delete only the letters in each cell so that the data would read:
4
3
8
2-6
2

I know that there has to be an easy solution to this, I just can't find it.  Any help would be very much appreciated

Jennifer

### RE: delete only letters from a field with numbers and letters

Create a form from the table that has your field. Create another unbound text box. Put on the OnCurrent event of the form the following:(Machine is a field with machine name combining alphas and numeric eg. 2aaa, a4b, etc. Text8 is the name of my unbound textbox)

Private Sub Form_Current()
Dim i As Integer
Dim j As Integer
Dim holda As String

j = Len(Me![machine])
For i = 1 To j
holda = Mid(Me![machine], i, 1)
If InStr(1, "1234567890", holda) Then
Else
hold = hold & holda
End If
Next i
Me![Text8] = hold
End Sub

Neil

### RE: delete only letters from a field with numbers and letters

Ooops. Got it backwards, you want to keep the numbers. Move the "hold = ..." up.

Private Sub Form_Current()
Dim i As Integer
Dim j As Integer
Dim holda As String

j = Len(Me![machine])
For i = 1 To j
holda = Mid(Me![machine], i, 1)
If InStr(1, "1234567890", holda) Then
hold = hold & holda
Else
End If
Next i
Me![Text8] = hold
End Sub

Neil

### RE: delete only letters from a field with numbers and letters

You don't need to create a form to do this.  You could put the above coding into a Public Function (let's call it StripNonNumerics).  You can then run an update query against the table to update the field:

UPDATE mytable
SET myfield = StripNonNumerics([myfield]);

Cruising the Information Superhighway

### RE: delete only letters from a field with numbers and letters

Mr. Wemeier,

An excellent suggestion! Thanks.

Neil

### RE: delete only letters from a field with numbers and letters

(OP)
It worked perfectly. Thanks to both of you for your help.

Jennpen1

### RE: delete only letters from a field with numbers and letters

Is there then a simple command to strip odd characters?  I have text in a field like this:

11/23/2001 CAR SOLDìON 10-24-2001ìCUSTOMER TO PICK UP.ì
#40545.

I need to remove these funny characters and insert spaces.  These "i" characters are followed by solid | type characters only they look bold.  They are not showing up here when I paste the text, but I see them clearly in form view and datasheet view within access (maybe these are line feeds?)  Not sure how they were created.  My data is imported by a .dbf file.

If anyone knows how to strip these stupid characters out of my memo field within the db I'd appreciate the help!!!!

### RE: delete only letters from a field with numbers and letters

I've been up late doing some homework.  Microsofts Knowledgebase explains this problem the solution is detailed here:

RESOLUTION
You can create an Access Basic procedure to remove these characters from the Memo field. The procedure should go through each record in the table and copy the Memo field, character by character, to a temporary holding area, ignoring all instances of Chr(161). The procedure should then copy the corrected string back into the Memo field.

Unfortunately I don't know how to do this...but at least now I know it is Chr(161).  Would anyone be kind enough to put together some code or point me in the right direction???

### RE: delete only letters from a field with numbers and letters

Chr(161) is just ONE example of what is refered to as "High ASCII".  If you want to 'rid' your data of (all of) these, you need to know / decide what "characters" are acceptable.  The numerals and letters (lower case and Upper case) are relatively easy, but wheather you want various special symbols and puncutation is less clear, depending on your specific app / business rules.

Generally, the "printable" character set is considered to be those between Chr(32) (the Space) and Chr(126) (the Tilde), although Chr((95) is also "White space"  and a fwe  ew others anr not common in ENGLISH language:

Chr(94) = Caret (^)
Chr(96) = Grave (`)

On the other hand, Chr(10) and Chr(13) are the "Carriage Return" and "Line Feed" symbols commonly used to generate a "new line".

So, to REALLY strip out the unwanted "sttuuufff", you need to set up a routine SIMILAR to the one shown here - but check for the specific characters (or character ranges) which you want to include (or use negative logic and filter out the excluded ones) and place desired ones back into the field.

A simple procedure to illustrate:

Public Function basFltrChr(strIn As String) As String

Dim Idx As Long
Dim MyChr As String * 1
Dim strTemp As String

Idx = 1
While Idx <= Len(strIn)

MyChr = Mid(strIn, Idx, 1)
Select Case MyChr

Case Is = vbCr
'Retain
strTemp = strTemp & MyChr

Case Is = vbLf
'Retain
strTemp = strTemp & MyChr

Case Is = vbTab
'Retain
strTemp = strTemp & MyChr

Case Space(1) To "^"
'Retain
strTemp = strTemp & MyChr

Case "a" To "}"
'Retain
strTemp = strTemp & MyChr

Case Else
'Reject

End Select

Idx = Idx + 1
Wend

basFltrChr = strTemp

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

### RE: delete only letters from a field with numbers and letters

Thanks Michael!

I searched tek-tips for 3 hours last night with no luck...I think i'm just not using the right lingo if you know what I mean (I still don't tottally understand what a string is and how you can use it)

The only character(s) I am having trouble with is the "i" character and the funny |that doesn't appear when I paste it here.  They always show together.  I need to replace these two with a single space.  So just eliminating them is a start but won't completely solve my problem.  This is ONLY occurs in the memo field "Remarks", in my table "Records".

I'll play with the code that you sent and check that thread, thanks for your help.  If you think of anything else please let me know!

Nick

### RE: delete only letters from a field with numbers and letters

I need a way of striping non numerical characters from say an invoice# field.  At this point I just don't know how modify the code and create the public function.

The code discussed so far:
Private Sub Form_Current()
Dim i As Integer
Dim j As Integer
Dim holda As String

j = Len(Me![machine])
For i = 1 To j
holda = Mid(Me![machine], i, 1)
If InStr(1, "1234567890", holda) Then
hold = hold & holda
Else
End If
Next i
Me![Text8] = hold
End Sub

I need to adapt this code to run in the update query, so obviously all the references to the form controls will be gone.

Wemeier stated:

You don't need to create a form to do this.  You could put the above coding into a Public Function (let's call it StripNonNumerics).  You can then run an update query against the table to update the field:

UPDATE mytable
SET myfield = StripNonNumerics([myfield]);

Can anyone tell me how to go about making this a public function to run in an update query?-- I'm in way over my head here and trying to catch up!

Thanks!

### RE: delete only letters from a field with numbers and letters

When in VBE (Alt+F11) menu Insert -> Module
Public Function StripNonNumerics(myVar)
Dim s As String, i As Long, x As String
If Trim(myVar & "") <> "" Then
s =""
For i = 1 To Len(myVar)
x = Mid(myVar, i, 1)
If x >= "0" And x <= "9" Then s = s & x
Next i
StripNonNumerics = s
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

### RE: delete only letters from a field with numbers and letters

~~ Two (2) years between posts to this thread.  ~~ Two years membership.  ~~ 16 total posts.  Are you actually doing any programming?  If not, why be here at all?  What is the point / purpose of these exercises?

MichaelRed

### RE: delete only letters from a field with numbers and letters

I know this looks rediculous to you MichaelRed.  I wasn't sure if I should start a new thread or not.

I'm learning....albeit at a very slow and often interupted pace.

I got started on this project over two years ago.  It got shelved when we lost personnel and I'm back on it.

I wish I was "actually" doing programming.  Sadly I have to attend to my current business responsibilities while working on this at the same time.

This is a casual hobby for me with some real world potential in my line of work.

Especially you MichaelRed

### RE: delete only letters from a field with numbers and letters

Here is a Clean String function which I often use to remove unwanted characters from data.  I hope that it will be of some use ...

#### CODE

Function to remove all characters which are not 'allowed' from a string.

Function CleanString(strOneLine As String) As String

Dim I As Integer

Dim strOutLine As String
Dim strOneChar As String
Dim strAllowed As String

'---------------------------------------------------
'- Set up a string of allowed characters.  In this -
'- case, A to Z and a to z plus single quote '     -
'---------------------------------------------------
strAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'"

'---------------------------------------------------
'- If an empty string is passed to the function,   -
'- just exit.                                      -
'---------------------------------------------------
If strOneLine = "" Then
strOutLine = ""
Exit Function
End If

'---------------------------------------------------
'- Build an output string containing the valid     -
'- characters from the input string                -
'---------------------------------------------------

For I = 1 To Len(strOneLine)
strOneChar = Mid$(strOneLine, I, 1) If InStr(strAllowed, strOneChar) > 0 Then strOutLine = strOutLine & strOneChar End If Next I CleanString = strOutLine End Function Bob Stubbs ### RE: delete only letters from a field with numbers and letters Here is a Clean String function which I often use to remove unwanted characters from data. I hope that it will be of some use ... #### CODE Function CleanString(strOneLine As String) As String Dim I As Integer Dim strOutLine As String Dim strOneChar As String Dim strAllowed As String '--------------------------------------------------- '- Set up a string of allowed characters. In this - '- case, A to Z and a to z plus single quote ' - '--------------------------------------------------- strAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'" '--------------------------------------------------- '- If an empty string is passed to the function, - '- just exit. - '--------------------------------------------------- If strOneLine = "" Then strOutLine = "" Exit Function End If '--------------------------------------------------- '- Build an output string containing the valid - '- characters from the input string - '--------------------------------------------------- For I = 1 To Len(strOneLine) strOneChar = Mid$(strOneLine, I, 1)
If InStr(strAllowed, strOneChar) > 0 Then
strOutLine = strOutLine & strOneChar
End If
Next I

CleanString = strOutLine

End Function

Bob Stubbs

### RE: delete only letters from a field with numbers and letters

PVH - Works perfectly thanks!

BobStubbs - Great robust example...nice to have something that is easily modified for other uses!

Thanks for all of the help guys!  I think I can put this one to bed now.

:)

#### 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!