# 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

The top one (I used cell A2) will always be 1...then in B3, try this:

=IF((RIGHT(A3,LEN(A3)-2)*1)-(RIGHT(A2,LEN(A2)-2)*1)>9,B2+1,B2)

that assumes there are always 2 letters at the beginning and only numbers beyond that.

• 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

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

Not a problem. Glad to help :cheers:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!