Function for formatting data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top