S
StratfordWard
Hello All:
We are trying to update about 8,000 customer records and unfortunately
inherited some fields that heretofore had very few input parameters. We have
fixed that problem but still have some fields, such as phone number fields,
with messy data.
Does anyone know how I can write an expression in an Access Query that will
return all the numeric characters in a field and eliminate all the
non-numeric (a-z, punctuation, spaces, etc.)? It would be ideal if something
like Val() would work on this type of data but Val() doesn't seem to work
here... I guess I could write a really ugly replace(replace(replace...
expression but am wondering if there is a better way.
Here are a few examples of what we have in some of our fields and, of
course, all we want is numbers....i.e. all should become "1234567890"
(123) 456-7890
(123)456-7890
123-456-7890
123.456-7890
123.456.7890
123.456.7890 FAX
Fred Jones-(123) 456-7890
(123) 456-7890 Ext 123
123.456.7890
(123) 456-7890
(123)4567890
(123)456-7890
123.456.7890
123-4567890
Alan Jones 123-456-7890 Fax #
Fred Jones 123-456-7890
In each of the above lines, I would want to extract just "1234567890"
except, of course, where there's an extension number.
Thanks in advance for any ideas!
Stratford
We are trying to update about 8,000 customer records and unfortunately
inherited some fields that heretofore had very few input parameters. We have
fixed that problem but still have some fields, such as phone number fields,
with messy data.
Does anyone know how I can write an expression in an Access Query that will
return all the numeric characters in a field and eliminate all the
non-numeric (a-z, punctuation, spaces, etc.)? It would be ideal if something
like Val() would work on this type of data but Val() doesn't seem to work
here... I guess I could write a really ugly replace(replace(replace...
expression but am wondering if there is a better way.
Here are a few examples of what we have in some of our fields and, of
course, all we want is numbers....i.e. all should become "1234567890"
(123) 456-7890
(123)456-7890
123-456-7890
123.456-7890
123.456.7890
123.456.7890 FAX
Fred Jones-(123) 456-7890
(123) 456-7890 Ext 123
123.456.7890
(123) 456-7890
(123)4567890
(123)456-7890
123.456.7890
123-4567890
Alan Jones 123-456-7890 Fax #
Fred Jones 123-456-7890
In each of the above lines, I would want to extract just "1234567890"
except, of course, where there's an extension number.
Thanks in advance for any ideas!
Stratford