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

See response in excel.misc

--
HTH

Bob Phillips

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

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

I'm sure I have answered this somewhere else

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

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553173

Thanks so much for the prompt response. I must apologise for crossposting
to the other Excel groups but I have to confess that I wasn't sure which
group I should post in.

Thanks again

Jem


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

Jeremy,

If you want to hit many groups, do them all in the same message. When we
read them they then get read in all groups.

--
HTH

Bob Phillips

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

"Jeremy Turner" wrote in message
...
Thanks so much for the prompt response. I must apologise for crossposting
to the other Excel groups but I have to confess that I wasn't sure which
group I should post in.

Thanks again

Jem


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