delete only letters from a field with numbers and letters 3

[Jennpen1]

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

 

[fneily]

Create a form from the table that has your field. Create another unbound text box. Put on the OnCurrent event of the form the followingMachine 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

 

[fneily]

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

 

[wemeier]

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)}

 

[fneily]

Mr. Wemeier,

An excellent suggestion! Thanks.

Neil

 

[Jennpen1]

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

Jennpen1

 

[nja5150]

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!!!!

 

[nja5150]

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???

 

[MichaelRed]

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:

[tab]Chr(94) = Caret (^)
[tab]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 &quot;^&quot;
                'Retain
                strTemp = strTemp & MyChr

            Case &quot;a&quot; To &quot;}&quot;
                '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

 

[nja5150]

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

 

[nja5150]

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!

 

[PHV]

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

 

[MichaelRed]

~~ 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

 

[nja5150]

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

 

[BobStubbs]

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

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

 

[BobStubbs]

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

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

 

[nja5150]

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.