Anne Troy wrote...
=if(b2="valid",a2,trim(b2))
Then, custom format the column that contains this formula as 0000000
....

You should know better. Number formats don't affect text values (well,
the 4th part of a #;#;#;# format does, but Excel won't accept 0000000
as the 4th part of a custom number format).

If the OP is seeing entries with leading zeros, then either those
entries must be text, so unaffected by number formatting, or numbers
with a custom number format already applied.

You failed to notice the OP's statement that the VALID/INVALID
indicators aren't in the dataset, but were included only to show which
entries were valid and which weren't.

You also failed to notice that OP's ultimate target for this data is
Access, and Access does squat all with Excel custom number formats.

And since when has Excel's TRIM function removed any character other
than the ASCII space character? Looks like the OP wants to remove
zeros. [Looks like you need to spend more time reading postings more
carefully.]

"Phil" wrote in message
....
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.

You mean 9 decimal digits and letters. Any letters or just a few?

In any case, it looks like you mean a *single* leading zero followed by
an alphanumeric character other than zero (or only 1-9?) followed by 8
more alphanumeric characters.

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.

Looks like any entry beginning with 2 or more leading zeros is invalid.
Is that the case? If so, invalid entries X satisfy MID(X,2,1)="0".

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

TRIM only removes spaces, not zeros. Using SUBSTITUTE to replace zeros
with spaces then passing the result to TRIM, then using SUBSTITUTE
again to convert the remaining spaces back to zeros would be dangerous
because it'd delete trailing runs of zeros and truncate any inner runs
of zeros.

Should the invalid entries be shortened? That is, should |0000212462|
become |0212462| or do they need to be padded back out to 10
characters? If they only need to be truncated,

=IF(MID(A1,2,1)<"0",A1,
MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )="0",0)-1,10))

If they need to be padded on the right with zeros,

=IF(MID(A1,2,1)<"0",A1,
LEFT(MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9, 10},1)="0",0)-1,10)
&"000000000",10))