CODEWITHSUNDEEP
stringexcelsumformulaalphanumeric
mandy yu
mandy yu

Reputation: 3

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

Upvotes: 0

Views: 1978

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

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 --> [

Upvotes: 1

j__m
j__m

Reputation: 9625

What you need is a single character that will never be used. Let's say that it's &.

Armed with this never-used character, you can then do it like this:

Change all of your Z's to &.
Change all of your Y's to Z.
Change all of your X's to Y.
Change all of your W's to X.
.
. etc., etc.
.
Change all of your &'s to 0.

Upvotes: 0

Related Questions