Extracting the numbers from the Text Field

[Irshad Alam]

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

 

[XPS350]

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

 

[Irshad Alam]

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

 

[John Spencer]

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

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

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
.

 

[Ken Snell]

Put this function in a regular module, then call it from your query:

SELECT PrimaryKeyField, StripAllNonNumericChars(YourFieldName)AS Numbers
FROM Yourtablename;




'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************

' ** This function strips all nonnumeric characters from a text string.

Function StripAllNonNumericChars(varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function

 

[Irshad Alam]

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

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

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

On 17 apr, 09:40, Irshad Alam <[email protected]>
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
.

.

 

[Irshad Alam]

Dear Sir,


Thank you very much for your code, which worked perfect.

Note: Noticed that it takes time only comparison to above code but does not
produces error.

Any tips.

Another question related just for my knowledge/learning, that if a situation
will arise to extract only Text from the text field, then what ammendment to
be done in the below code, which you have advised. It will help me to learn
and implement if any such situation arises in future.


Thanking you.

Regards

Irshad

 

[PieterLinden via AccessMonster.com]

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>

 

[Ken Snell]

Not sure what you mean by "Noticed that it takes time only comparison to
above code..."?

Here is a different function that strips numbers from a string:

'****************************
'* *
'* Fxn StripNumberChars *
'* *
'****************************

' ** This function strips all numeric characters from a text string.

Function StripNumberChars(strOriginalString As String) As String
Dim intLoop As Integer
Dim strTemp As String
On Error Resume Next
strTemp = strOriginalString
For intLoop = Asc("0") To Asc("9")
strTemp = Replace(strTemp, Chr(intLoop), "", 1, -1, vbTextCompare)
Next intLoop
StripNumberChars = strTemp
Err.Clear
End Function

 

[Irshad Alam]

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

 

[Irshad Alam]

Sir,

Thanks for your reply.

Regarding my noticed/Note: I meant to say that the reply/code I received
from Mr. Pieter Linden also works, but prdocues #Error if the referred field
is blank, if it is not blank it works. Faster in comparison to your code.
While your provided code works perfect, but consume more time in comparison.

Regarding the new code below your provided for extracting the alphabets from
the field, it works, but produces #Error if the Referred field is blank. I
used in the query as below :

GetAlpha : StripNumberChars([PDIJob])

It works, if the field is blank, it shows #Error

Please advice the changes required.

Thanks and best regards

Irshad

 

[John Spencer]

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

 

[John Spencer]

Function StripNumberChars(strOriginalString As Variant) As String
Dim intLoop As Integer
Dim strTemp As String
On Error Resume Next
strTemp = strOriginalString
For intLoop = Asc("0") To Asc("9")
strTemp = Replace(strTemp, Chr(intLoop), "", 1, -1, vbTextCompare)
Next intLoop
StripNumberChars = strTemp
Err.Clear
End Function

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

Sir,

Thanks for your reply.

Regarding my noticed/Note: I meant to say that the reply/code I received
from Mr. Pieter Linden also works, but prdocues #Error if the referred field
is blank, if it is not blank it works. Faster in comparison to your code.
While your provided code works perfect, but consume more time in comparison.

Regarding the new code below your provided for extracting the alphabets from
the field, it works, but produces #Error if the Referred field is blank. I
used in the query as below :

GetAlpha : StripNumberChars([PDIJob])

It works, if the field is blank, it shows #Error

Please advice the changes required.

Thanks and best regards

Irshad

Not sure what you mean by "Noticed that it takes time only comparison to
above code..."?

Here is a different function that strips numbers from a string:

'****************************
'* *
'* Fxn StripNumberChars *
'* *
'****************************

' ** This function strips all numeric characters from a text string.

Function StripNumberChars(strOriginalString As String) As String
Dim intLoop As Integer
Dim strTemp As String
On Error Resume Next
strTemp = strOriginalString
For intLoop = Asc("0") To Asc("9")
strTemp = Replace(strTemp, Chr(intLoop), "", 1, -1, vbTextCompare)
Next intLoop
StripNumberChars = strTemp
Err.Clear
End Function


--

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




.

 

[Marshall Barton]

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.

 

[Ken Snell]

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.

 

[John Spencer]

To expand on Ken Snell's observation.

If you are checking one character at a time then IsNumeric will return false
for any character that is not in the range 0 to 9.

If you are checking an entire string of multiple characters at once then you
can end up with unexpected results if all you want is just the number
characters.

IsNumeric can return true if there are commas or only one period in the
string. Also it can return true if the string can be read as a scientific
notation number (1.043E4) or (1.034D5). And it seems to ignore
one currency symbol (at least the $ sign)
one negative sign
one plus sign
parentheses around the number.

Probably a few other things to. Basically if a human (computer savvy) would
read the string as a just number then IsNumeric seems to return true.

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