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

=?Utf-8?B?VGhlIENob21w?=
Guest
Posts: n/a

 29th Apr 2006
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

Brendan Reynolds
Guest
Posts: n/a

 29th Apr 2006

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

--
Brendan Reynolds
Access MVP

"The Chomp" <(E-Mail Removed)> wrote in message
news:542E662A-E445-473E-AE1F-(E-Mail Removed)...
>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
>
>
>

=?Utf-8?B?VGhlIENob21w?=
Guest
Posts: n/a

 30th Apr 2006
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

"Brendan Reynolds" wrote:

>
> 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
>
> --
> Brendan Reynolds
> Access MVP
>
> "The Chomp" <(E-Mail Removed)> wrote in message
> news:542E662A-E445-473E-AE1F-(E-Mail Removed)...
> >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
> >
> >
> >

>
>
>

=?Utf-8?B?VGhlIENob21w?=
Guest
Posts: n/a

 2nd May 2006
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

"The Chomp" wrote:

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

John Spencer
Guest
Posts: n/a

 2nd May 2006
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
"The Chomp" <(E-Mail Removed)> wrote in message
news24EB2A8-5B1B-4A8C-B8A7-(E-Mail Removed)...
> 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
>
> "The Chomp" wrote:
>
>> 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
>>
>>
>>

John Nurick
Guest
Posts: n/a

 2nd May 2006
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/C...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").

On Tue, 2 May 2006 11:40:02 -0700, The Chomp
<(E-Mail Removed)> wrote:

>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
>
>"The Chomp" wrote:
>
>> 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
>>
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

=?Utf-8?B?VGhlIENob21w?=
Guest
Posts: n/a

 4th May 2006
Thanks all. These worked great. Now if I can get the people who enter the
data to do it correctly.

"John Nurick" wrote:

> 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/C...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").
>
>
> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
> <(E-Mail Removed)> wrote:
>
> >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
> >
> >"The Chomp" wrote:
> >
> >> 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
> >>
> >>
> >>

>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>

John Nurick
Guest
Posts: n/a

 4th May 2006
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.

On Thu, 4 May 2006 03:07:01 -0700, The Chomp
<(E-Mail Removed)> wrote:

>Thanks all. These worked great. Now if I can get the people who enter the
>data to do it correctly.
>
>
>
>"John Nurick" wrote:
>
>> 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/C...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").
>>
>>
>> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
>> <(E-Mail Removed)> wrote:
>>
>> >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
>> >
>> >"The Chomp" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>

>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

=?Utf-8?B?VGhlIENob21w?=
Guest
Posts: n/a

 6th May 2006
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

"John Nurick" wrote:

> 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.
>
>
>
> On Thu, 4 May 2006 03:07:01 -0700, The Chomp
> <(E-Mail Removed)> wrote:
>
> >Thanks all. These worked great. Now if I can get the people who enter the
> >data to do it correctly.
> >
> >
> >
> >"John Nurick" wrote:
> >
> >> 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/C...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").
> >>
> >>
> >> On Tue, 2 May 2006 11:40:02 -0700, The Chomp
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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
> >> >
> >> >"The Chomp" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >>
> >> --
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
> >>

>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Cass1930 Microsoft Excel Worksheet Functions 2 19th Mar 2010 03:18 PM ramulu via OfficeKB.com Microsoft Excel Worksheet Functions 1 21st Feb 2007 03:32 PM ramulu Microsoft Excel Worksheet Functions 1 21st Feb 2007 11:00 AM =?Utf-8?B?UGF1bCBCYXNz?= Microsoft Excel Programming 2 27th Jul 2006 04:37 PM G.Adamson Microsoft Excel Misc 6 12th Oct 2003 06:30 AM

Features