Hi all,

I'm a newbie with very little excel experience. I have a problem which I would very much apprieciate the assistance of you experts with.

I have a series of numbers in a list. Each of the numbers in the list can be up to 12 digits long (but can also be shorter):-

541140590
541140592
541140593
541140594
541140595
541140596
541140597
541140598
541140599
54114060
54114061
54114062
54114064
54114065
54114066
54114067
54114068
54114069
54114070
54114071
54114072
54114073
54114074
54114075
54114076
54114077
54114078
54114079
54114080
54114081
54114082
54114083
54114084
54114085
54114086
54114087
54114088
54114089

In this example (from the list above) the first set of numbers are nine digit long starting 541140590. If I truncate these to the 8th digit they would all be the same, right? So it would be fair to say that 'if' the nineth digit of the first block covered the complete range of 0-9, truncating would not be a problem. However, as you can see the number 541140591 is missing from the range, so I do not want the range truncated.

The next block of numbers are eight digits long starting from 54114060. Like the first set, by the considering the last digit (ie the eighth digit in this case) on each of the numbers doesn't cover the full range of 0-9 as 54114063 is missing. These should also not be truncated.

However, the next (third) set are also eight digits long starting from 54114070. Unlike the first and second set of numbers the last digit (ie the eigth digit) on each of the numbers covers the full range of 0-9 (with none missing). This means that these can and should be truncated to seven digits (namely 5411407). And so on for the next groups of numbers startomg 54114080. Which can (following the above rules) be truncated to 5411408.

The question is, how do I get MS Excel to do this for me, either by usings a macro, or a formula which I can paste into an adjacent cell, that will show any truncated numbers together with those that (following the rules above) should not truncated (so that I can drag it down)?

Thanks in advance of your anticipated assistance.

Kind regards

On Sat, 17 Nov 2012 23:54:52 +0000, greenemm wrote:


Hi all,

I'm a newbie with very little excel experience. I have a problem which I
would very much apprieciate the assistance of you experts with.

I have a series of numbers in a list. Each of the numbers in the list
can be up to 12 digits long (but can also be shorter):-

541140590
541140592
541140593
541140594
541140595
541140596
541140597
541140598
541140599
54114060
54114061
54114062
54114064
54114065
54114066
54114067
54114068
54114069
54114070
54114071
54114072
54114073
54114074
54114075
54114076
54114077
54114078
54114079
54114080
54114081
54114082
54114083
54114084
54114085
54114086
54114087
54114088
54114089

In this example (from the list above) the first set of numbers are nine
digit long starting 541140590. If I truncate these to the 8th digit they
would all be the same, right? So it would be fair to say that 'if' the
nineth digit of the first block covered the complete range of 0-9,
truncating would not be a problem. However, as you can see the number
541140591 is missing from the range, so I do not want the range
truncated.

The next block of numbers are eight digits long starting from 54114060.
Like the first set, by the considering the last digit (ie the eighth
digit in this case) on each of the numbers doesn't cover the full range
of 0-9 as 54114063 is missing. These should also not be truncated.

However, the next (third) set are also eight digits long starting from
54114070. Unlike the first and second set of numbers the last digit (ie
the eigth digit) on each of the numbers covers the full range of 0-9
(with none missing). This means that these can and should be truncated
to seven digits (namely 5411407). And so on for the next groups of
numbers startomg 54114080. Which can (following the above rules) be
truncated to 5411408.

The question is, how do I get MS Excel to do this for me, either by
usings a macro, or a formula which I can paste into an adjacent cell,
that will show any truncated numbers together with those that (following
the rules above) should not truncated (so that I can drag it down)?

Thanks in advance of your anticipated assistance.

Kind regards

If there are no duplicate numbers, then,
if the first value is in A1
and ColA is a Named Range representing all the numbers in column A
then

B1: =IF(SUMPRODUCT(--(INT(ColA/10)=INT(A1/10)))=10,INT(A1/10),A1)

and fill down as far as required.

Hi Ron,

Firstly, thank you for taking the time to look into helping me solve my 'problem'.

Your solution has saved me many hours of tedious and monotonous manual checking. I am very grateful. Thank you so much.

Kind regards

Michael

On Sun, 18 Nov 2012 11:30:03 +0000, greenemm wrote:

Hi Ron,

Firstly, thank you for taking the time to look into helping me solve my
'problem'.

Your solution has saved me many hours of tedious and monotonous manual
checking. I am very grateful. Thank you so much.

Kind regards

Michael

Glad to help. Thanks for the feedback. Note that the solution depends critically on the absence of duplicate numbers. If there might be duplicates, a different solution will be required.