Add 1 if more than last digit changes, otherwise add 0

Dear all

I need help with the following issue in EXCEL: I am having a long list (column) with characters and numbers (designations); now I need a second column indicating whether only the last or more digits changed between the designations in the first column. If only the last digit changes in the first column, the number (index from 1 to xxx) in the second column should stay stable, if more digits change, this index should be increased by 1.

I.e.:

[TABLE="width: 10"]

[tr]

[td]

Designation

[/td]

[td]

Index

[/td]

[/tr]

[tr]

[td]

AE145231

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td]

AE145232

[/td]

[td]

1

[/td]

[/tr]

[tr]

[td]

AE148236

[/td]

[td]

2

[/td]

[/tr]

[tr]

[td]

AE745265

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

AE1775231

[/td]

[td]

4

[/td]

[/tr]

[tr]

[td]

AE1775232

[/td]

[td]

4

[/td]

[/tr]

[tr]

[td]

...

[/td]

[td]

...

[/td]

[/tr]

[/TABLE]

I was trying to use "if" and "match", but didn't find the solution - any help is very much welcome!

Best,
Chechi

Re: Add 1 if more than last digit changes, otherwise add 0

Very nice - this did the job perfectly and is easier than I expected...!

Thank you very much!
Chechi