Function for formatting data

D

D Huber

Is there a function that will allow me to only pull the numeric data from a
field? Currently I have a field with letters and numbers, but I want to
isolate the numbers.

Current field How I want it
530146374D 530146374
ABFS004163635 4163635
AD43285 43285
151652777 151652777

I have tried using the built in functions (left, right, mid, etc. ) in a
query, but have had little success because there are so many formats to the
current field. Is there a simple fuction that just looks at the numbers and
ignores all letters? If one does not exist, would it be possible to create a
function that will perform this task?
 
A

Allen Browne

You can write your own function to loop through each character of the
string, and determine if it is a digit (i.e. Asc() returns vbKey0 to
vbKey9.)

But do you allow a decimal point? Just one?
Do you allow a leading/trailing plus/minus?
Brackets around a negative?
Scientific format?

One of the basic rules of data normalization is that your fields must be
atomic, i.e. you don't put different kinds of things in one field. Perhaps
this is import data that you are trying to parse into its fields. If not,
you might want to consider redesigning the table.

Val() parses the leading digits if that's any help.
 
K

Klatuu

Here is a function that will select only "0" throught "9" As a string
Public Function NumbersOnly(strAll) As String
Dim strNumbers As String
Dim strTest As String
Dim intCtr As Integer
Dim intLength As Integer

intLength = Len(strAll)
For intCtr = 1 To intLength
strTest = Mid(strAll, intCtr, 1)
If IsNumeric(strTest) Then
strNumbers = strNumbers & strTest
End If
Next intCtr

NumbersOnly = strNumbers
End Function
 
Top