Extract Numbers from a text field whereever they are

[Sheila D]

If I have a text field and the data starts with numbers I know I can use VAL
to extract the number but if the data starts with a letter VAL returns 0 -
i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the
text data actually is? I'm hoping there is a function for this as I don't
really write code. Thanks for any help

 

[fredg]

If I have a text field and the data starts with numbers I know I can use VAL
to extract the number but if the data starts with a letter VAL returns 0 -
i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the
text data actually is? I'm hoping there is a function for this as I don't
really write code. Thanks for any help

Their is a function, but unfortunately it's User Defined, therefore
you have to write it yourself. I've done it for you here.

If the string is going to be like "A109", where the number value is
contiguous, then you can use:

Copy and paste the below code into a Module

Function FindNumbers(strIn As String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 49 And intX <= 58 Then
FindNumbers = Val(Mid(strIn, intY))
Exit For
End If
Next intY

End Function
_____________

You can call it using
=FindNumbers([FieldName])


However, if the string can be like "A1B0XVF9Z", where the number
values are intermixed with letter values and you wish to extract all
the numbers, then you have to do something like this:

Copy and paste the below code into a module.

Public Function FindAllNumbers(strIn as String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 48 And intX <= 58 Then
FindAllNumbers = FindAllNumbers & Mid(strIn, intY, 1)
End If
Next intY

End Function
________________

You call this the same way:
=FindAllNumbers([FieldName])

Both functons will return 109 from their respective string.

 

[Sheila D]

Fred you are a star, thank you

If I have a text field and the data starts with numbers I know I can use VAL
to extract the number but if the data starts with a letter VAL returns 0 -
i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the
text data actually is? I'm hoping there is a function for this as I don't
really write code. Thanks for any help

Their is a function, but unfortunately it's User Defined, therefore
you have to write it yourself. I've done it for you here.

If the string is going to be like "A109", where the number value is
contiguous, then you can use:

Copy and paste the below code into a Module

Function FindNumbers(strIn As String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 49 And intX <= 58 Then
FindNumbers = Val(Mid(strIn, intY))
Exit For
End If
Next intY

End Function
_____________

You can call it using
=FindNumbers([FieldName])


However, if the string can be like "A1B0XVF9Z", where the number
values are intermixed with letter values and you wish to extract all
the numbers, then you have to do something like this:

Copy and paste the below code into a module.

Public Function FindAllNumbers(strIn as String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 48 And intX <= 58 Then
FindAllNumbers = FindAllNumbers & Mid(strIn, intY, 1)
End If
Next intY

End Function
________________

You call this the same way:
=FindAllNumbers([FieldName])

Both functons will return 109 from their respective string.

 

[KARL DEWEY]

You can create a table Alpha with fields ALPHA containing all the characters
you may encounter. Use it in a totals query unjoined provided you have a
primary key. Field [y] text field has numbers and number-letter combinations.

SELECT [Change Requests].Primary_Key, [Change Requests].[Date open], [Change
Requests].[Date close], [Change Requests].y, Val(Replace([y],[ALPHA],"")) AS
[Number Extract]
FROM ALPHA, [Change Requests]
GROUP BY [Change Requests].Primary_Key, [Change Requests].[Date open],
[Change Requests].[Date close], [Change Requests].y,
Val(Replace([y],[ALPHA],""));