Extract numbers from text field

Can you elaborate more?
Do you want to extract 20 from 1st example, 120 from second example and 9 from 3rd example?

Thanks all. Nice and simple as usual!!

How would I use that in a query field

Create a small helper function. Then use this function in your query.

unfortunately, you cannot use the Split() function directly in a query.

I use a function

Public Function fnParseText(ParseWhat As Variant, Delimiter As String, Position As Integer) As Variant

          Dim myArray() As String

10        If IsNull(ParseWhat) Then
20            fnParseText = Null
30        Else
40            myArray() = Split(ParseWhat, Delimiter)
50            If Position = 0 Or Position > UBound(myArray) + 1 Then
60                fnParseText = Null
70            Else
80                fnParseText = Trim(myArray(Position - 1))
90            End If
100       End If
          
End Function

Select allOpen in new window

From a query, you might do something like:

SELECT Field1, Field2, (fnParseText(Field2, "x", 1) & "") as Expr1, fnParseText(Field2, "x", 2) as Expr2
FROM yourTable

Select allOpen in new window

But this will return a string, not a numeric value.  If you need it numeric, you might try using the above as a subquery:

SELECT T.Field1, T.Field2, IIF(IsNumeric(T.Expr1), Val(T.Expr1), T.Expr1) as Expr1, IIF(IsNumeric(T.Expr2), Val(T.Expr2), T.Expr2) as Expr2
FROM (
SELECT Field1, Field2, (fnParseText(Field2, "x", 1) & "") as Expr1, fnParseText(Field2, "x", 2) as Expr2
FROM yourTable
) as T

Select allOpen in new window

Not used many functions is this it?

Dim StopWidth As Single
Public Function SW()   'Calculates StopWidth by splitting Stops into width and thickness
    StopWidth = Val(Split(Stops.Value, "x")(0))
End Function

Naturally it does not work

Minor changes:

Dim StopWidth As Long

Public Function SW(ByVal InputValue As Variant)  As Long

    'Calculates StopWidth by splitting Stops into width and thickness
    StopWidth = Val(Split(InputValue, "x")(0))

End Function

Select allOpen in new window

Sorry Dale your response got to me after my last post somehow.

Does your Function fnParseText return a numeric value and if I can get it to work is the query field title be something like  Width: fnParseText

is there no simple way in a query to change 19 as text into 19 as a value?

Yes:

NumValue: Val([TextVaueField])

Select allOpen in new window

Thanks Gustav. The problem is as I have to apply this to an existing table I need the solution as a field in a query and nothing I'm trying works.

You could upload a demo.

I tried that but unfortunately  or fortunately it works. But not I'm afraid on my native data. There is obviously an issue somewhere else. I have recreated the conditions faithfully but it does convert or read the numbers as a value. If I put criteria in the nuber field such as >0 on the sample it work on the native data I get message "Data type mismatch or query expression" even when I use Width: Val(Left([Stops],2))

You may have Null  values, so try:

NumValue: Val(Nz([TextVaueField]))

Select allOpen in new window

Thanks Gustav