Finding a 4 digit number in a text field and copy to a new field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that contains text and a 4 digit number. the 4 digit number is
not always in the same position in the field. What I am trying to do is
locate the number in the field and then copy the number only to a new field.
The one problem is. The record could look like this " I would like 3434 to be
part" or it could look like this "I would like3434 to be part".
This is been driving me nuts for a while...
Thanks for any help
 
This assumes that the text does not contain any other digits ...

Public Function DigitsFromText(ByVal TextIn As String) As String

Const strcDigits As String = "0123456789"

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To Len(TextIn)
strChar = Mid$(TextIn, lngLoop, 1)
If InStr(1, strcDigits, strChar) > 0 Then
strWork = strWork & strChar
End If
Next lngLoop

DigitsFromText = strWork

End Function
 
Thanks Brendan,

The text may contain more digits but I would only be after the first
grouping of 4 digits.

I will give this a try.

Thanks again
 
Good Afternoon.

This worked great. I diod run into a weeee bit of a problem. It seems that
there is can be more that one set of numbers in the field (just can't get
people to enter the right stuff).
After a review of all the records in the field, it seems that the only
number I need worry about is the "left most" number.
Anyone have anything that would help.

Thanks all
 
You could try the following modification to the original code.
Public Function DigitsFromText(ByVal TextIn As String) As String

Const strcDigits As String = "0123456789"

Dim strWork As String
Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To Len(TextIn)
strChar = Mid$(TextIn, lngLoop, 1)
If InStr(1, strcDigits, strChar) > 0 Then
strWork = strWork & strChar
'Got 4 consecutive number characters so stop
If Len(strWork) = 4 Then Exit For
Else
'Clear strWork if the digits aren't consecutive
strWork = vbnullstring
End If

Next lngLoop

DigitsFromText = strWork

End Function
 
One approach is to use a regular expression to identify the first group
of 4 digits in the string. If you copy the rgxExtract function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm into a
code module, you can use something like this, where XXX is a variable
(or in a query, the field) containing the text:

rgxExtract(XXX, "\d{4}")

\d means a digit 0-9, and {4} means four of them, so the expression gets
the first four contiguous digits in the string. E.g. if you give it "xxx
12345 xxx678 xxx9999xxx" it will return "1234".

If necessary one can get much more subtle. For instance this

rgxExtract(XXX, "^(?:.*?\D)?(\d{4})\D")

will get the first substring of exactly 4 digits (in the example above
it skips the 12345 and 678 and returns "9999").
 
Thanks all. These worked great. Now if I can get the people who enter the
data to do it correctly.
 
That's much more difficult<g>. Can you split up this field so they enter
the 4-digit number in one place and the text next to it? That would make
it comparatively easy to validate the number.
 
I have tried to get people to at least stick to a standard when entering a
number. I asked that the 4 digit number be the "first then a space". For some
reason not everyone can figure this out.
As for splitting the text field, the text field is the "descriptionfield "
of a change action request (transport) in SAP. This is linked to third party
software for migrating managing change action requests.

The Chomp
 
Back
Top