Help with Condensing 10 Digit Number Please

[Paul Black]

Hi Everyone,

I have a List of Numbers that are Produced Using a Formula which are ALL
10 Digits Long :-

0112110000
0221000001
0221100000
0011201100
0011300100

What I would Like is to Condense ( Using a Formula ) EACH 10 Digit
Number Down to ONLY Digits Greater Than One. For Example :-

Number 0112110000 Becomes 11211
Number 0221000001 Becomes 2211
Number 0221100000 Becomes 2211
Number 0011201100 Becomes 11211
Number 0122100000 Becomes 11221

I have Tried Left, Right, Trim etc But Without Any Success.
Does Anyone have Any Ideas Please.

Thanks in Advance
All the Best
Paul

 

[Thomas Ramel]

Grüezi Paul

Paul Black schrieb am 03.02.2005

0112110000
0221000001
0221100000
0011201100
0011300100

What I would Like is to Condense ( Using a Formula ) EACH 10 Digit
Number Down to ONLY Digits Greater Than One. For Example :-

Number 0112110000 Becomes 11211
Number 0221000001 Becomes 2211
Number 0221100000 Becomes 2211
Number 0011201100 Becomes 11211
Number 0122100000 Becomes 11221

With the last one I don't agree ;-)

....but try the following formula:


=SUBSTITUTE(A1;"0";"")



Regards
Thomas Ramel

 

[Bob Phillips]

Hi Paul,

Try this

=SUBSTITUTE(SUBSTITUTE(A1,"1",""),"0","")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Paul Black]

Thanks Thomas & Bob.
You are Quite Right Thomas, the Number 0122100000 Should Become 1221 NOT
11221, My Error.

Thanks Again to you Both.
All the Best
Paul

 

[Bob Phillips]

Was it greater than 1 or greater than 0? Your text was ambiguous.

--

HTH

RP
(remove nothere from the email address if mailing direct)