Formula to return sequence (e.g.: AA + 1 = AB; AZ + 6 = BF)

[enoctis]

Although this is similar to other resolved posts, I was unable to find one that matches my specific need.

As the title states, I need to be able to specify a starting string, and increase the sequence by a specified number. For example:

If starting string is AA and
The number to increase by is 5 then
The result should be AF

Using the example above, the desired formula accepts the starting string and the "increase by" number to result in the new AA notation.

CG plus 26 should return DG.

Preferably, the formula would do the same for triple-character notation. Meaning BZZ plus 3 would hopefully return CAC.

Any assistance is immensely appreciated. Although I could get this to work, I'm certain that my formula would be incredibly cumbersome and very resource hungry. Thank you guys, in advance, for your help!

 

[lrobbo314]

How about this?

ExpandIP.xlsm
	A
1	AF
2	DG
Sheet10

Cell Formulas
Range	Formula
A1	A1	=xl("AA",5)
A2	A2	=xl("CG",26)

Function XL(s As String, n As Long)
XL = Split(Range(s & "1").Offset(, n).Address, "$")(1)
End Function

 

[lrobbo314]

Or a non-VBA solution.

ExpandIP.xlsm
	C	D	E	F
1	AA	5		AF
2	CG	26		DG
3	BZZ	3		CAC
Sheet10

Cell Formulas
Range	Formula
F1:F3	F1	=SUBSTITUTE(LEFT(CELL("address",OFFSET(INDIRECT(C1&"1"),,D1)),SEARCH("$",CELL("address",OFFSET(INDIRECT(C1&"1"),,D1)),2)-1),"$","")

 

[CatyH]

this is a fun problem! a good example of modular

A1	Enter data:	ABC
A2	Formula	=IF(LEN(A1)>=3,LEFT(RIGHT(A1,3)),"")
A3	Formula	=FIND(A2,"ABCDEFGHIJKLMNOPQRSTUVWXYZ")
A4	Formula	=ROUNDDOWN(B4/26,0)
A5	Formula	=A4+A3
A6	Formula	=CHOOSE(A5,"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")
B1	Enter data:	5​
B2	Formula	=IF(LEN(A1)>=2,LEFT(RIGHT(A1,2)),"")
B3	Formula	=FIND(B2,"ABCDEFGHIJKLMNOPQRSTUVWXYZ")
B4	Formula	=ROUNDDOWN(C4/26,0)
B5	Formula	=B4+B3
B6	Formula	=CHOOSE(B5,"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")
C2	Formula	=IF(LEN(A1)>=2,LEFT(RIGHT(A1,2)),"")
C3	Formula	=FIND(C2,"ABCDEFGHIJKLMNOPQRSTUVWXYZ")
C4	Formula	=C3+B1
C5	Formula	=MOD(C4,26)
C6	Formula	=CHOOSE(C5,"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")

 

[enoctis]

I do like coding, a LOT. However, the computers I need to use this on are in a secure area, on a secure network. Therefore, all macros are disabled. I really appreciate the effort, but I can't use your code. Cheers!

 

[enoctis]

I do like coding, a LOT. However, the computers I need to use this on are in a secure area, on a secure network. Therefore, all macros are disabled. I really appreciate the effort, but I can't use your code. Cheers!

This simply didn't go where I meant it to. It's been a long time since I used standard BBS

 

[CatyH]

I do like coding, a LOT. However, the computers I need to use this on are in a secure area, on a secure network. Therefore, all macros are disabled. I really appreciate the effort, but I can't use your code. Cheers!

did you try lrobbo314's non-vba solution? i'm SO curious to know how/why it works!

 

[enoctis]

Or a non-VBA solution.

ExpandIP.xlsm
	C	D	E	F
1	AA	5		AF
2	CG	26		DG
3	BZZ	3		CAC
Sheet10

Cell Formulas
Range	Formula
F1:F3	F1	=SUBSTITUTE(LEFT(CELL("address",OFFSET(INDIRECT(C1&"1"),,D1)),SEARCH("$",CELL("address",OFFSET(INDIRECT(C1&"1"),,D1)),2)-1),"$","")

This is seemingly PERFECT. I wouldn't have come up with this solution on my own, for sure. Thank you, so much. Your response will enable me to create the WIP spreadsheet that will save me HOURS every day. Kudos!