CODEWITHSUNDEEP
excel-formula
NewUser
NewUser

Reputation: 1

Max formula ignoring text

I am trying to display the next ID number using the max formula for a column that contains an ID number with both text and number (e.g., XXX0043). Can someone help?

Upvotes: 0

Views: 263

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

assuming your IDs are in A1:A10, and the numeric part is always the last 4 characters:

=MAX(--right(A1:A10,4))

use CONTROL+SHIFT+ENTER when confirming the formula instead of just ENTER. You will know you have done it right when { } show up around your formula. Note that the { } cannot be added manually.

POC

UPDATE: Based on Solar Mikes comment

="xxx"&RIGHT("0000"&MAX(--right(A1:A10,4)),4)

With the assumption that xxx does not change...if xxx changes then it a little more complicated.

Upvotes: 2

Related Questions