Need help with TRIM function
Harlan Grove wrote...
....
....
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".
....
Stupid of me. The test for invalidity should be LEFT(X,1)="00".
Which would make the formulas
(truncated)
=IF(LEFT(A1,2)<"00",A1,
MID(A1,MATCH(FALSE,MID(A1,{1,2,3,4,5,6,7,8,9,10},1 )="0",0)-1,10))
(padded on the right with zeros)
=IF(LEFT(A1,2)<"00",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))
|