Assuming that you only want to have the VALID values from your table, which
contain a numeric, and you want to strip the leading zeros from the 9 digit
number so that the following would be true - the (9) digit numeric 012012000
would end up as an (8) digit numeric of 12012000...

You can use the following formula to produce a table that you can either
filter or place into a pivot table to isolate your "VALID" entries...
=IF(ISERROR(VALUE(A4)),,VALUE(A4))
What this will do is to produce a numeric entry without leading zeroes and
produce a "0" entry from the list for those that are alpha/numeric...


--
Thanks for your help -
Joe Mac


"Phil" wrote:

Hello,

I have a table with these values, some of which are INVALID.

0120120001 VALID
0000212462 INVALID
0150214785 VALID
04874A1234 VALID
0005421424 INVALID
00000A1240 INVALID
0014A27870 INVALID

As you can see, from above:
1 - There are values that are only numeric.
2 - There are ALSO values that are BOTH numeric and text.
3 - You will also notice that there is a only ONE type of pattern in these
numbers and that is with the numbers that begin with a 0 (zero), then have 9
numbers following, eg 04874A1234.
4 - To clarify, I have indicated which are the VALID and INVALID values,
please disregard the mentioning of the actual VALID and INVALID text, it is
NOT in the datasets.

I need to run a TRIM function that will:
a) disregard any values that are VALID,
b) trim off ALL of the leading zeros EXCEPT the last zero in ANY of the
INVALID's.

How can this be done?

BTW, since I will be importing this into Access, and Access DOES NOT like
mixed datatypes, ALL of the values will need to be TEXT (or should they be
GENERAL?).

TIA for your replies.

Phil.