Text to number

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

Hi,

Thanks for your help

I have a text field that contains number & text in the
same field. How do I turn it into numbers only ie


old required
inv420 420
cr0056 56
boos23897 23897
147apt 147


the field is call ref

Thanks onece again
 
Hi,

Thanks for your help

I have a text field that contains number & text in the
same field. How do I turn it into numbers only ie

old required
inv420 420
cr0056 56
boos23897 23897
147apt 147

the field is call ref

Thanks onece again
Click to expand...


If the number was always at the beginning of the field, i.e. 147apt,
you could use:
Required = Val([Old])

But as long as the numbers can be anywhere within the field you'll
need to check each character.
If the numbers in the field are going to be all together (abc123) as
in your sample data, and not mixed up within the field (ab4cv5k6) the
following will work for you.

You need to create a user defined function to cycle through the value
in each record until you find the first number character:

In a Module:

Function ValNumber(strString As String) As Integer
On Error GoTo Err_Handler

Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
Resume Exit_ValNumber

End Function
===
You can call it from a query:
Exp:(ValNumber([FieldName])

As criteria in the query use:
Where [Old] is not null

Or in the control source of an unbound control in a report or form:
=IIf(Not IsNull([Old]),ValNumber([Old],"")
 
Here is a function that can be used to get the numbers only.

You can change the constant if you want to return other characters than just the numbers.

Public Function StripToCharsOnly(ByVal varText As Variant) As String
'Takes input and returns only the specified characters in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strChars As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strChars, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

StripToCharsOnly = strOut

End Function
 
Back
Top