Excel FIND and REPLACE functions for unique numbering-CodePudding

I have the below Excel formula to help auto-number rows on a sheet to be unique. I want to FIND the first occurrence of the dash character (-) and add 1 to the number immediately to the right of it.

Here is the formula I am using to do that: It find the number to the right of the first dash (5) and adds 1 to it, resulting in a value of 6.

=CONCATENATE(LEFT(A11,7),"-",REPLACE(A11,1,FIND("-",A11),"") 1)

This works fine if there is only one dash (-) character in the string being searched, however my data will have 3 dashes in it, and when I have a string with multiple dashes in it, then I get a #VALUE error. How can I fix this to find the number to the right of the first dash in the string and add 1 to it in this case?

CodePudding user response：

I would put these formulas, substituting where I put [cell] with the cells your referencing. It's an ugly formula, I know but it breaks this down a few ways.

First, it takes the leftmost characters prefixing the number to do math to and adds that text to the calculation. The calculation separates all the text before and after that numbers, converts it to value, adds 1, then combines that to the prefix. Lastly, it takes the suffix and adds it to the prefix and mid.

There might be an opportunity to use a Mid formula; however, I was unsure how many characters the number could reach so I kept this variable.

=LEFT([Cell],8) & VALUE(RIGHT(LEFT([Cell],FIND("-",[Cell],9)-1),LEN(LEFT([Cell],FIND("-",[Cell],9)-1))-FIND("-",LEFT([Cell],FIND("-",[Cell],9)-1)))) 1 & "-" & RIGHT([Cell],LEN([Cell])-FIND("-",[Cell],9))

Using your cell references:

=LEFT(A11,8) & VALUE(RIGHT(LEFT(A11,FIND("-",A11,9)-1),LEN(LEFT(A11,FIND("-",A11,9)-1))-FIND("-",LEFT(A11,FIND("-",A11,9)-1)))) 1 &"-"& RIGHT(A11,LEN(A11)-FIND("-",A11,9))

CodePudding user response：

Since you're using 7 as a constant number in your LEFT function, i'm assuming that the 7characters at the beginning are constant. So with that assumption, you can simply use below formula to automatically add the numbers

=CONCAT(LEFT(A11,7),"-",MID(A11,9,IFERROR(FIND("-",A11,9),LEN(A11) 1)-9) 1)