How do I trim cell values

[Jeremy Turner]

Hi

I have a column of cells each containing a 4 digit number. Is there any way
I can trim the left digit dependant upon certain conditions.

If the left digit of each 4 digit number equals either "1" or "2" I want to
trim this away. If the original 4 digit number ends in anything other than
"1" or "2" I want to leave the original 4 digit number unaltered.

For example:

1231 would become 123
1232 would become 123
1234 would stay as 1234

Any help on this is really appreciated.

Many thanks in advance

Jem

 

[Mallycat]

Assuming your data is in A1

=IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2"),RIGHT(A1,3),A1)

 

[Bob Phillips]

=--LEFT(A1,LEN(A1)-(OR(RIGHT(A1)="1",RIGHT(A1)="2")))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

=IF(OR(RIGHT(A1)=1,RIGHT(A1)=2),LEFT(A1,3),A1)

 

[Guest]

There appears to be a conflict between your description and your example.
This formula will return results in accordance with your Example..........

=IF(OR(RIGHT(A1,1)="1",RIGHT(A1,1)="2"),LEFT(A1,LEN(A1)-1),A1)*1

Vaya con Dios,
Chuck, CABGx3

 

[Jeremy Turner]

Many thanks for everyones prompt responses. It really is appreciated.

Cheers

Jem

 

[Jeremy Turner]

Oop's... That's me not knowing my right from left ;-)

Thanks for the help, i've now got it sorted.

Cheers

Jem