extracting numbers

[DrJimmy]

hi,

I have lines of data that is made up of a reference number (7 digits) and names. I want to be able to extract just the reference number from the string. The reference number is sometimes at the front, in the middle or at the end - which I have no control over.

Does anyone know of a way that I can run a script or query to do this?

Cheers

DrJimmy

 

[datAdrenaline]

Assuming the reference number can be returned as a number, and is a contiguous string within the entire string ... then this function should be able to achieve your desired result ..

[COLOR=black][SIZE=3][FONT=Times New Roman]Public Function fGetReferenceNumber(strText As String) As Long[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]    Dim x As Integer[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]    Dim btByte() As Byte[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]    btByte = strText[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]    For x = 0 To UBound(btByte) Step 2[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]        If (btByte(x) >= 48 And btByte(x) <= 57) Then _[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]            fGetReferenceNumber = Val(Mid(strText, x \ 2 + 1)): Exit For[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]    Next x[/FONT][/SIZE][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT][/COLOR]

If you need the result with leading 0's then just wrap the function call in the Format() function ...

Format(fGetReferenceNumber(<your string>),"0000000")

...

Hope that helps!

 

[MSAccessRookie]

hi,

I have lines of data that is made up of a reference number (7 digits) and names. I want to be able to extract just the reference number from the string. The reference number is sometimes at the front, in the middle or at the end - which I have no control over.

Does anyone know of a way that I can run a script or query to do this?

Cheers

DrJimmy

One of the basic rules of Parsing requires that the target string have a definable start and end (see below). If that is not the case, then I have no other suggestions at this time.


Possibilities:

• It could be the only Numeric substring in the string such as
  • "abcdefghijkl12345mno"
  • "12345abcdefghijklmno"
  • "abcdefghijklmno12345"
• It could always follow the character "&" and end with the character "#" (the special characters only appearing as markers).
  • "abcdefghijkl&12345#mno"
  • "&12345#abcdefghijklmno"
  • "abcdefghijklmno&12345#"

 

[smiler44]

Hi,
I hope I can explin this properly. To try and make it easier I have broken the code down into 3 routines. place 5 text boxes onto a new project. in text 1 put in 1234567abc, in text2 type abc1234567def and in text 3 type abc1234567

also put on 3 command buttons. command 1 will deal with text1, command2 will deal with text2 and command 3 will deal with text 3.

text4 will show the result. text 5 is used by the code to get the string of 7 numbers that you want. You will be able to create 1 routine from the 3 I have created.

the code in command 2 works if your string of 7 numbers starts at either the 2nd, 3rd or 4th charector from the left. if it is 6th, 7th 8th etc you will need to add more else's and if's but hopefully you can see that and work out how to do it.


the code is
Private Sub Command1_Click()
'''''''''''''''''''''''''''
'check to see if first digit is text or number
scan = Text1.Text
Text5.Text = Mid$(scan, 1, 1)
If IsNumeric(Text5.Text) Then
Text4.Text = Mid$(scan, 1, 7)
Else
'''''''''''''''''''''''
MsgBox ("not number")
End If
End Sub

Private Sub Command2_Click()
'finds numbers in middle
scan = Text2.Text
Text5.Text = Mid$(scan, 2, 1)
If IsNumeric(Text5.Text) Then
Text4.Text = Mid$(scan, 2, 7)
Else
Text5.Text = Mid$(scan, 3, 1)
If IsNumeric(Text5.Text) Then
Text4.Text = Mid$(scan, 3, 7)
Else
Text5.Text = Mid$(scan, 4, 1)
If IsNumeric(Text5.Text) Then
Text4.Text = Mid$(scan, 4, 7)
End If
End If
End If
End Sub

Private Sub Command3_Click()
'check to see if last number digit
Dim text_length As Integer
Dim target As String ' target is all of string minus 7
' get length of string
scan = Text3.Text
text_length = Len(Text3.Text)
target = text_length - 6
Text5.Text = Mid$(scan, target, 7)
If IsNumeric(Text5.Text) Then
Text4.Text = Text5.Text
Else
MsgBox ("not all numbers")
End If
End Sub

 

[datAdrenaline]

Smiler44 ....

As a point of note. The .Text property can not be set or referenced unless the control that has the property has the focus.