Parse out into new table

[GavZ]

Hi,

i have many fields in an excel with data in them for example:

hhfdksha1234567890oliidjjmw
dhskhfajkldshfljk09876543210!fdsopfd
ds1112223330!fbjdi

is it possible to search through the table and parse out just the 10 digit number and store it in a new table so its just:

1234567890
9876543210
1112223330

Thanks in advance!

 

[Galaxiom]

Check out the IsNumeric function. Loop through the string in the cell until you find a numeric character then use some logic.

 

[GavZ]

OK what else i should have said is that im a bit of a noob to this. do you have any code references i can look at and once i find the numeric value how do i get it to save in a new field?

 

[Galaxiom]

Basically you ned to use the Mid function.

Mid has arguments for the first and number of characters. So you loop though a variable that is the first argument and use something like

y = Len(fieldname)
 
For x = 1 To y
   z = Mid(fieldname, x, 1)
   If IsNumeric(z) Then: somevariable = somevariable & z
Next

You will need more logic to handle the multiple instances of the numeric string.

 

[GavZ]

I am struugling to work this out - is it possible to do it with an append query?

 

[Galaxiom]

You can certainly use an Append query but you will need a custom function to extract the numerals from the string.

If you are ever going to get far in Access you will need to learn some VBA. Looks like it is time to start.

Use this in your query

NewField: ExtractNumber(fieldname)

In a Standard Module:

Public Function ExtractNumber(somestring As String) as String
 
Dim str As String
Dim x As Integer
Dim y As Integer
Dim z As String
 
y = Len(somestring)
 
For x = 1 To y 
    z = Mid(somestring, x, 1) 
    If IsNumeric(z) Then
        str = str & z
    ElseIf Len(z) <> 0 Then
        Exit For
    End If
Next
 
ExtractNumber = str
 
End Function

This function will extract the first contiguous group of numerals from the string in the field.

 

[GavZ]

Wow - you really are amazing, it works!!

The only other thing is that it returned some fields blank (i suspect that they didnt have any numbers) and some that only had 3 numbrs in it. is there any way to limit it to just find 11 digit numbers (if it helps they all start with 0)

Thanks again

 

[GavZ]

dont worry i put Len([f3])=11 in the criteria and it works perfectly!

thanks again for all your help