Extracting number from string

Question

I have a field in access that has data like this:

23

23 Days

2 Weeks

Net 14 Days

I need to get the number from all these lines

I need

23

23

2 (although best would be 14 because 2 * 7 days)

14 

The Val function works fine for all the above except for the net 14 days which results in 0 since it stops when it sees the first non numeric character which is "n". Is there a way to get the position of the first numeric character and then do the val function on that, or any other ideas?

---

Debra has a question

Answer 1

The Val function works fine for all the above except for the net 14 days which results in 0 since it stops when it sees the first non numeric character which is "n". Is there a way to get the position of the first numeric character and then do the val function on that, or any other ideas?

Hi Debra,

You could make a function, and pass the data through a parameter.

Within the function you use the Split-function using the space as separator.

The loop through the array elements from 0 to UBound(array).

The first array element that passes the test IsNumeric, gives you the numeric value. If the next array element is available, and has a value of "weeks" or "week" or "w", then you multiply the found value with 7.

Finally return the final value as result of the function.

Imb.

Answer 2

Thank you for your quick response. I'm new to access I'm really importing data from access to sql and I would like to import it with the proper numeric value since the column in sql is an integer column. When importing from access to sql you have to make a query in visual basic I think so I'm not very familiar and I'm just looking around for a simple in line statement that can be used within the query. Would this do the trick and would you be able to spell out the exact function for me? 

---

Debra has a question

Answer 3

Add the following function to a standard module in the database:

Function GetDays(var)

    Dim n As Integer
    Dim intDays As Integer
   
    If Not IsNull(var) Then
        For n = 1 To Len(var)
            If IsNumeric(Mid(var, n, 1)) Then
                intDays = Val(Mid(var, n)) * IIf(InStr(var, "weeks") > 0, 7, 1)
                Exit For
            End If
        Next n
        GetDays = intDays
    End If

End Function

You can then call it wherever necessary in the database, e.g. in a computed column in a query or a computed control in a form or report, or in VBA code.  You can see how it works in the debug window:

? GetDays("23")
 23
? GetDays("23 Days")
 23
? GetDays("2 Weeks")
 14
? GetDays("Net 14 Days")
 14

---

Ken Sheridan, Stafford, England

Answer 4

You can use this sub as a starting point:

Option Explicit
Const theField = "23 23 Days 2 Weeks Net 14 Days"
Sub test()
    Dim i As Integer
    Dim fieldLength As Integer
    Dim num As Integer
    Dim start As Integer
    Dim c As String

    fieldLength = Len(theField)
    For i = 1 To fieldLength
        c = Mid(theField, i, 1)
        If IsNumeric(c) Then      'Number found, extract it
            start = i
            Do While IsNumeric(c)
                i = i + 1
                c = Mid(theField, i, 1)
            Loop
            '*
            '* Get this number into an integer
            '*
            num = Mid(theField, start, i - start)
            '*
            '* Show our extracted number (debug only)
            '*
            Debug.Print num
        End If
    Next i
End Sub

Will print out:
23
23
2
14

---

Best regards, George

Answer 5

You can also try using the Mid and Val functions together in a query expression as below:

=IIf(Val([YOUR FIELD NAME) = 0,CDbl(Mid([YOUR FIELD NAME], 4, 4)),Val([YOUR FIELD NAME]))

This assumes that the Length parameter is 4 (Net + the space before 14). If it is different, you will need to adjust the Length parameter.

Answer 6

With all respect, Lawrence, but how do you dynamically adjust how long a number is in her data fields with your method?

---

Best regards, George

Answer 7

You can't George. She asked how it might be used in a single line in a query. But as I said, unless the length is fixed as 4 (as in Net+space), she would need to adjust the formula.

Answer 8

This code will allow you to directly use an SQL string to query the database and return the results in a Collection:

Option Compare Database
Option Explicit

Const DELIMITERCHAR = "," ' Set to the delimiter between numbers that you want

Sub showMe()
    Dim db As Database
    Dim rs As Recordset
    Dim myNumbers As New Collection
    Dim tempNums As Variant
    Dim i As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT myTable.StrNum, GetNum([StrNum]) AS theNumbers FROM myTable;")
    Do While Not rs.EOF
        '*
        '* Extract the numbers to an array of integers (example only)
        '* Optionally you can include a row identifier
        '*
        tempNums = Split(rs!theNumbers, DELIMITERCHAR)

        For i = 0 To UBound(tempNums)
            myNumbers.Add (tempNums(i))	  'Insert the numbers into the Collection
        Next i
        rs.MoveNext
    Loop
    '*
    '* Show result
    '*
    For i = 1 To myNumbers.Count    'Collections start index is 1, not 0
      Debug.Print myNumbers(i)
    Next i
End Sub
Function GetNum(theField As String) As String
    Dim i As Integer
    Dim fieldLength As Integer
    Dim num As Integer
    Dim start As Integer
    Dim c As String
    Dim theNumbers As String
    Dim delimiter As String

    delimiter = ""
    theNumbers = ""
    fieldLength = Len(theField)
    For i = 1 To fieldLength
        c = Mid(theField, i, 1)
        If IsNumeric(c) Then      'Number found, extract it
            start = i
            Do While IsNumeric(c)
                i = i + 1
                c = Mid(theField, i, 1)
            Loop
            '*
            '* Get this number into a string
            '*
            num = Mid(theField, start, i - start)
            theNumbers = theNumbers & delimiter & num
            delimiter = DELIMITERCHAR    'To avoid delimiter first in string
        End If
    Next i
    GetNum = theNumbers
End Function

Will display:
23
23
2
14

---

Best regards, George

Answer 9

This is working fine in access itself, but I don't know how to integrate it into the sql import wizard (I don't think it can use functions). 

---

Debra has a question

Answer 10

This is to specified for a specific example but if there is a way to make such an inline statement in the query without functions I would really appreciate it.

---

Debra has a question

Answer 11

This is to specified for a specific example but if there is a way to make such an inline statement in the query without functions I would really appreciate it.

---

Debra has a question

Hi Debra. Pardon me for jumping in, but if you have to manipulate the data, then you'll need to use a function. Val() itself is a function, although a built-in one. So, if you can use Val() in your import process, then you can also use any of the functions provided above. Just my 2 cents...

Answer 12

We have never used SQL Server, so I have no experience of its import capabilities, but if it will only import from a base table, not a query, which would surprise me, then you could call the function in an 'append' query in Access to insert rows into a predefined base table, and then import from that.

As regards returning 3 by default you simply need to initialise the intDays variable to 3 before looping through the string expression:

Function GetDays(var)

    Dim n As Integer
    Dim intDays As Integer
    
    intDays = 3

    If Not IsNull(var) Then
        For n = 1 To Len(var)
            If IsNumeric(Mid(var, n, 1)) Then
                intDays = Val(Mid(var, n)) * IIf(InStr(var, "weeks") > 0, 7, 1)
                Exit For
            End If
        Next n
        GetDays = intDays
    End If

End Function

---

Ken Sheridan, Stafford, England

Answer 13

It doesn't only import from a base table it does work also with a query. But, the query is in access language but not actually in access so I don't know how to call the function in the query. But I can fiddle around with a table field using the Mid, val, instr ... That is why I was wondering how to do this with an in line query. If you would know of a way to use the function within the query I would greatly appreciate it since it is working beautifully in access. How would I be able to call the function in an 'append' query in Access to insert rows into a predefined base table?

---

Debra has a question

Answer 14

By 'query' I was really referring to an Access querydef object, rather than to the use of the term generically.

---

Ken Sheridan, Stafford, England

Answer 15

Hi Debra,

>> But I can fiddle around with a table field using the Mid, val, instr ... That is why I was wondering how to do this with an in line query. If you would know of a way to use the function within the query I would greatly appreciate it since it is working beautifully in access.

Where do you want to use this function, in SQL Server? Based on your description, it works in Access, could you share us more about where it did not work? If you mean you want to use this function in SQL Server, I am afraid it is impossible, you will need SQL Server function instead of Access function. Or, I would suggest you get the right data in Access first with Access function, and then import the data to SQL Server again.

If I misunderstood you, please feel free to let us know where you want to achieve this function and more information about your situation.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.