×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

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!

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

Students Click Here

delete only letters from a field with numbers and letters
3

delete only letters from a field with numbers and letters

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
       (your mileage may vary)

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

    'Michael Red    7/3/03  Tek-Tips Thread700-562429

    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.

To answer you question:

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.

I appreciate all the help you guys have give me.

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.

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! Already a Member? Login

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