split a field

[Khalil Handal]

Hi,
I have an access database with a file that is alpha numeric where number and text forms the data.
How can I spit this field into two different fields where the first field contains the numbers only and the second field contains the rest of the original one.

sample:
Original ========== field 1 ===========field 2
240DIZ ===========240 ==============DIZ
268004SAMR 4-2 =====268004 =========SAMR 4-2
109090034abcd 55 ====109090034 =======abcd 55
089011CTX =========289011 ==========CTX

Note: All data in the original field starts with numeric values.



The ====== is used to separate the three columns for clearance only in the post

Khalil

[JoeM]

I would probably create some User Defined Functions (UDFs), like done here: http://www.utteraccess.com/forum/fin...-t1766615.html

[June7]

Last record shows 089011 becomes 289011 - must be a typo.

Val([original]) will return the numbers at beginning but will drop leading zeros. So if leading zeroes must be considered, second part can be returned with:

Mid([original], Len(CStr(Val([original])) + IIf(Left([original],1)=0,2,1))

Consistency in structure is critical when parsing text.

[Khalil Handal]

Hi,
Last record in the sample is a mistake, it should be 089011.

I have 36 records with leading zeros.
I used the formula and was able to get the second part but with a leading number at the begining (sample: 9TAC 4-2), I was able to delete the first digit.

Is there a way to keep a leading zero in a numeric field othat than the Format?

Thanks to all

[orange]

The only way to keep leading 0 is to treat data as string/text.

Here's a sample routine using your data as test data.

Sub testMay19()
10  On Error GoTo testmay19_Error
    Dim i As Integer
    Dim numpart As String, otherpart As String
    Dim str(3) As String
    'test data
20  str(0) = "240DIZ"
30  str(1) = "268004SAMR 4-2"
40  str(2) = "109090034abcd 55"
50  str(3) = "089011CTX"
    'process test data
60  For i = 0 To UBound(str)
70      If Left(str(i), 1) = "0" Then
80          numpart = "0" & val(str(i))
90      Else
100         numpart = val(str(i))
120     End If
        'process remainder of test data record
110     otherpart = Right(str(i), Len(str(i)) - Len(numpart))
130     Debug.Print str(i) & Space(20 - Len(str(i))) & " ---> " & "numpart: " & numpart & vbTab & "otherpart: " & otherpart
140 Next i


150 On Error GoTo 0
160 Exit Sub

testmay19_Error:

170 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testmay19, line " & Erl & "."

End Sub

And the results:
240DIZ ---> numpart: 240 otherpart: DIZ
268004SAMR 4-2 ---> numpart: 268004 otherpart: SAMR 4-2
109090034abcd 55 ---> numpart: 109090034 otherpart: abcd 55
089011CTX ---> numpart: 089011 otherpart: CTX

[Khalil Handal]

Thank you all for the help.
Solved

[orange]

What was your solution? It may help someone else.