A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extracting the numbers from the Text Field



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2010, 08:40 AM posted to microsoft.public.access.queries
Irshad Alam
external usenet poster
 
Posts: 44
Default Extracting the numbers from the Text Field

I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below :

456ZX
ZX965
ZX785M
ZX956MN
45NM96ZX

I need to retrieve only the numeric part like below
456
965
785
956
4596

I tried left, right, mid etc but failed. not sucess.

Please advice a solution to pull/extract only the number portions from a
text field

Regards

Irshad

  #2  
Old April 17th, 2010, 10:39 AM posted to microsoft.public.access.queries
XPS350
external usenet poster
 
Posts: 69
Default Extracting the numbers from the Text Field

On 17 apr, 09:40, Irshad Alam
wrote:
I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below :

456ZX
ZX965
ZX785M
ZX956MN
45NM96ZX

I need to retrieve only the numeric part like below
456
965
785
956
4596

I tried left, right, mid etc but failed. not sucess.

Please advice a solution to pull/extract only the number portions from a
text field

Regards

Irsha


You could build a function for this and use it in your query. It looks
like:

Function NumbersInString(WithNumbers As String) As Variant
Dim Temp As Variant
Dim T As Integer

For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next

NumbersInString = Temp
End Function


Groeten,

Peter
http://access.xps350.com
  #3  
Old April 17th, 2010, 01:55 PM posted to microsoft.public.access.queries
Irshad Alam
external usenet poster
 
Posts: 44
Default Extracting the numbers from the Text Field

Sir,
Thanks for your reply.

some more modification is required in the code.

I pasted the below code to a new module.

Called the function in the query as below :
JobNum: NumbersInString([PDIJob])

Its Extracing the number from the text field , perfect. BUT showing result
#Error
if the referred PDIJob field is blank.

I need if the field is blank, it should be blank instead of #Error

Please do the ammendment in the code and help.

Regards

Irshad


"XPS350" wrote:

On 17 apr, 09:40, Irshad Alam
wrote:
I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below :

456ZX
ZX965
ZX785M
ZX956MN
45NM96ZX

I need to retrieve only the numeric part like below
456
965
785
956
4596

I tried left, right, mid etc but failed. not sucess.

Please advice a solution to pull/extract only the number portions from a
text field

Regards

Irsha


You could build a function for this and use it in your query. It looks
like:

Function NumbersInString(WithNumbers As String) As Variant
Dim Temp As Variant
Dim T As Integer

For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next

NumbersInString = Temp
End Function


Groeten,

Peter
http://access.xps350.com
.

  #4  
Old April 17th, 2010, 02:34 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Extracting the numbers from the Text Field

Just change the first line to the following so the function can handle nulls
(blanks) as input.

Function NumbersInString(WithNumbers) As Variant

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Irshad Alam wrote:
Sir,
Thanks for your reply.

some more modification is required in the code.

I pasted the below code to a new module.

Called the function in the query as below :
JobNum: NumbersInString([PDIJob])

Its Extracing the number from the text field , perfect. BUT showing result
#Error
if the referred PDIJob field is blank.

I need if the field is blank, it should be blank instead of #Error

Please do the ammendment in the code and help.

Regards

Irshad


"XPS350" wrote:

On 17 apr, 09:40, Irshad Alam
wrote:
I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below :

456ZX
ZX965
ZX785M
ZX956MN
45NM96ZX

I need to retrieve only the numeric part like below
456
965
785
956
4596

I tried left, right, mid etc but failed. not sucess.

Please advice a solution to pull/extract only the number portions from a
text field

Regards

Irsha

You could build a function for this and use it in your query. It looks
like:

Function NumbersInString(WithNumbers As String) As Variant
Dim Temp As Variant
Dim T As Integer

For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next

NumbersInString = Temp
End Function


Groeten,

Peter
http://access.xps350.com
.

  #5  
Old April 18th, 2010, 05:34 AM posted to microsoft.public.access.queries
Irshad Alam
external usenet poster
 
Posts: 44
Default Extracting the numbers from the Text Field

Sir,

Thanks for your reply.

I tried as you advised below, it produces error no. 94

Please recheck and advice.

One additional question (just for my knowledge & learning) that if situation
is reversed (means it need to pull only text part then what ammendment to be
done in the above code.

Regards

Irshad

"John Spencer" wrote:

Just change the first line to the following so the function can handle nulls
(blanks) as input.

Function NumbersInString(WithNumbers) As Variant

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Irshad Alam wrote:
Sir,
Thanks for your reply.

some more modification is required in the code.

I pasted the below code to a new module.

Called the function in the query as below :
JobNum: NumbersInString([PDIJob])

Its Extracing the number from the text field , perfect. BUT showing result
#Error
if the referred PDIJob field is blank.

I need if the field is blank, it should be blank instead of #Error

Please do the ammendment in the code and help.

Regards

Irshad


"XPS350" wrote:

On 17 apr, 09:40, Irshad Alam
wrote:
I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below :

456ZX
ZX965
ZX785M
ZX956MN
45NM96ZX

I need to retrieve only the numeric part like below
456
965
785
956
4596

I tried left, right, mid etc but failed. not sucess.

Please advice a solution to pull/extract only the number portions from a
text field

Regards

Irsha
You could build a function for this and use it in your query. It looks
like:

Function NumbersInString(WithNumbers As String) As Variant
Dim Temp As Variant
Dim T As Integer

For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next

NumbersInString = Temp
End Function


Groeten,

Peter
http://access.xps350.com
.

.

  #6  
Old April 18th, 2010, 06:09 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Extracting the numbers from the Text Field

How about...

Function NumbersInString(Optional WithNumbers As String) As String
Dim Temp As Variant
Dim T As Integer

If IsMissing(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function

Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it
a little and it *seems* to work fine.

?numbersinstring("ABC1234DEF567G89H")
123456789

?numbersinstring()
returns Null

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

  #7  
Old April 21st, 2010, 05:34 AM posted to microsoft.public.access.queries
Irshad Alam
external usenet poster
 
Posts: 44
Default Extracting the numbers from the Text Field

Sir,

I tried your code, it produces #Error, if the reffered field is blank.

I used like below in query

GetJobNo: NumbersInString([PDIJobNo])

It fetch the number and perfect, if data is there in the PDIJobNo field, if
the field is blank, it show #Error.

Thanks and best regards

Irshad





"PieterLinden via AccessMonster.com" wrote:

How about...

Function NumbersInString(Optional WithNumbers As String) As String
Dim Temp As Variant
Dim T As Integer

If IsMissing(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function

Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it
a little and it *seems* to work fine.

?numbersinstring("ABC1234DEF567G89H")
123456789

?numbersinstring()
returns Null

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

.

  #8  
Old April 21st, 2010, 01:09 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Extracting the numbers from the Text Field

Try this modification if you are passing in values that might be null

Function NumbersInString(WithNumbers As Variant) As Variant
Dim Temp As String
Dim T As Integer

If IsNull(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PieterLinden via AccessMonster.com wrote:
How about...

Function NumbersInString(Optional WithNumbers As String) As String
Dim Temp As Variant
Dim T As Integer

If IsMissing(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function

Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it
a little and it *seems* to work fine.

?numbersinstring("ABC1234DEF567G89H")
123456789

?numbersinstring()
returns Null

  #9  
Old April 21st, 2010, 04:08 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Extracting the numbers from the Text Field

John Spencer wrote:

Try this modification if you are passing in values that might be null

Function NumbersInString(WithNumbers As Variant) As Variant
Dim Temp As String
Dim T As Integer

If IsNull(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function



I wonder if using Like "#" is faster or slower than
IsNumeric? At least Like generalizes to other sets of
characters more easily.

--
Marsh
MVP [MS Access]
  #10  
Old April 22nd, 2010, 04:13 AM posted to microsoft.public.access.queries
Ken Snell
external usenet poster
 
Posts: 177
Default Extracting the numbers from the Text Field

Also, IsNumeric will allow decimal points, etc. to remain as characters in
the string as well, so it sometimes is not a good choice when wanting just
numbers to remain.
--

Ken Snell
http://www.accessmvp.com/KDSnell/


"Marshall Barton" wrote in message
...
John Spencer wrote:

Try this modification if you are passing in values that might be null

Function NumbersInString(WithNumbers As Variant) As Variant
Dim Temp As String
Dim T As Integer

If IsNull(WithNumbers) Then
NumbersInString = Null
Else
For T = 1 To Len(WithNumbers)
If IsNumeric(Mid(WithNumbers, T, 1)) Then
Temp = Temp & Mid(WithNumbers, T, 1)
End If
Next
NumbersInString = Temp
End If

End Function



I wonder if using Like "#" is faster or slower than
IsNumeric? At least Like generalizes to other sets of
characters more easily.

--
Marsh
MVP [MS Access]



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.