How do I trim cell values

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

Assuming your data is in A1

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

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

--
HTH

Bob Phillips

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

"Jeremy Turner" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

"Jeremy Turner" wrote:

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

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" wrote:

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

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

Cheers

Jem


"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>

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

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

Cheers

Jem

"CLR" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>