How to strip back account numbers (1 Viewer)

[Number11]

So i now need to import some 43,00o records with account numbers layouts like this...


000009855114
or 557722 - Live Customer
or 200001144 - - Warehouse Code 44545

So how would i go about doping this was thinking along the lines of using LEN?

 

[arnelgp]

what do you need?
the numeric portion?

Public Function FirstNum(ByVal strText As String) As String
strText = Trim$(strText & "")
With CreateObject("vbscript.regexp")
    .Pattern = "^([0-9]+).+"
    .ignorecase = True
    .Global = False
    FirstNum = .Replace(strText, "$1")
End With
End Function

Debug.Print firstnum("200001144 - - Warehouse Code 44545")
Result: 200001144

 

[arnelgp]

Left$(theText, Instr(1, theText, " ")-1)

 

[Galaxiom]

If you don't care about leading zeroes and just want the numerals at the beginning:
Val(fieldname)

Two catches.
If the numerals are immediately followed by "e" and some more numerals it will be treated as exponential notation.
Val("123E45xyz") = 1.23E+47

Numbers over 2^31 -1 (2147483647) will be translated as floating point and probably lose detail.
32 bit Access, a probablylot bigger in 64 bit) I don't have Access at home at the moment,