trimming cell contents?
 

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

trimming cell contents?
 

Hi
you can use a worksheet function,

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

assumimng your number is in A1. The VALUE bit ensures a number is
returned
regards
Paul
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

trimming cell contents?
 

Thanks for the prompt reply, it's really appreciated !

Cheers

Jem


wrote in message
oups.com...
Hi
you can use a worksheet function,

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

assumimng your number is in A1. The VALUE bit ensures a number is
returned
regards
Paul
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

trimming cell contents?
 

Sub TrimData()
Dim l as Long, r as long
Dim cell as Range
for each cell in selection
if len(cell) = 4 and isnumeric(cell) then
l = Left(cell,1)
r = right(cell,1)
if (l = 1 or l = 2) and (r = 1 or r = 2) then
cell.Value = Right(cell.value,3)
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"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