Can you invoke fill series sequencing logic for mixed alphanumeric data within a function?

Copper Contributor

I have customer transaction item data in a file and am trying to add an alphanumeric invoice number field to each line item (like: 2019-FI-001) before importing them into an accounting application.  On each subsequent transaction line I wish to perform logic to determine if the customer number is the same as the previous line and if so keep the invoice number the same, otherwise increment by one.  I could do this by splitting the counter (001) and concatenating it to the prefix (2019-FI-) but wondering is there is a function that can do this like the fill series sequencing logic does automatically in a drag fill?

1 Reply
Assuming this example:
Customer Invoice
1 2019-FI-001
2 2019-FI-002
2 2019-FI-002
The formula in B3 is:
=IF(A3=A2,B2,
LEFT(B2,8)&
TEXT(RIGHT(B2,3)+1,"000"))