If a cell starts with the number 6 or7 or 8 or 9 then delete the last digit of that cell

[philipkroberts]

Hi guys

Pretty much as my title says. I have a set of data as below:

1068349
9908821
9886591
1065885
9550811
1082668
8995951

I want the output to look like this

1068349
990882
988659
1065885
955081
1082668
699595

Is there any formulae that can do this for me or should this be a Macro.
This would be a massive help to me!

I have another question however I shall make a new thread for it
I hope what I need is clear, I have a tendency to suck at explaining things.


Kind regards and thanks in advanced!
Philip

[shg]

If the values are always whole numbers with 15 or fewer digits,

=IF(LEFT(A1)<"6", A1, INT(A1/10))

[Speshul]

Assuming data starts in A1

=IF(OR(LEFT(A1)="6",LEFT(A1)="7",LEFT(A1)="8",LEFT(A1)="9"),LEFT(A1,LEN(A1)-1),A1)

[joeu2004]

[...deleted...]

[AlKey]

Or this

=IF(OR(LEFT(A1)+0={6,7,8,9}),REPLACE(A1,LEN(A1),1,""),A1)+0

	A	B
1	1068349	1068349
2	9908821	990882
3	9886591	988659
4	1065885	1065885
5	9550811	955081
6	1082668	1082668
7	8995951	899595