excelexcel-formula

excel adding 1 on all alphanumeric string


I need to make a new product code for 6000+ records. In order to do that fast and decipher it in the future, I need to put a formula from the old code to the new code.

sample codes:

yas111
bd224
sax112
sd00015
sp00114

If my formula is +1, the end result shoulb be:

zbt222   
ce335
tby223
te11126
tq11225

numbers will +1, and letters will increment to the next letter of the alphabet. I tried the substitute function, its working. but it cannot read both if there is a number that is already used. example:

Substitute(substitute(a1,"0","5"),"5","0")

It will only read 1. result will be

0 = 0
5 = 0

but I'm trying to get this result:

0 = 5
5 = 0

Solution

  • To do this in pure excel formula will be in my opinion unmanageably complex. The best way would be ti use a vba UDF

    Function IncrCode(rng As Range) As Variant
        Dim strOld As String, strNew As String
        Dim i As Long
    
        strOld = rng.Cells(1, 1).Value
        strNew = ""
        For i = 1 To Len(strOld)
            strNew = strNew & Chr$(Asc(Mid$(strOld, i, 1)) + 1)
        Next
        IncrCode = strNew
    End Function
    

    Call it like this

    =IncrCode(A1)
    

    if A1 = yas111 result would be zbt222

    Note that based on your question, all characters are incremented to the next ascii character, eg
    9 --> :
    z --> {
    Z --> [