FPS032Y22886104772-B-1
FPS032Y22904104773-B-1-E2
FPS032Y22904104774-B-1
FPS032Y22886104775-B-1
FPS032Y22886104776-B-1
FPS032Y22886104777-B-1
FPS032Y22886104778-B-1

I would like to increment a digit at a specific position of a unique serial number while keeping the rest of the serial intact. E.g. in the examples above the digit before the first '-' needs to be incremented.

What type of formula is suitable for that?

CodePudding user response：

Assuming that "number of number at end" implies incrementing '104788' (i.e. "-B-1" etc. then use:

=LET(x_,FILTERXML("<x><y>"&SUBSTITUTE(B2,"-B-1","</y><y>")&"</y></x>","//y"),y_,INDEX(x_,COUNTA(x_)-1),z_,SUBSTITUTE(y_,"FPS032Y",""),w_,z_ 1,SUBSTITUTE(B2,z_,w_))

RequiresOffice 35 cop5 Excel ompatibility). If, instead, you meant to refer to the numers ending 772,773,... etc. (which I

Calculation

In turn, this uses:

1. FilterXML to split the serial chain (delimitter = '-')
2. Index to identiy the final such serial 1
3. Substitute to remove unwanted text before adding 1 and sustituting result ack ito origina serial code

CodePudding user response：

Is there a possibility you can use this formula:

="FPS032Y22886104772-B-" & ROW()

As you can see, I just take the prefix and add the rownumber of the cell, how easy can it be? :-)