Have letters BN need formula to subtract by one = BM

[Dojorgen]

Hey guys quick question could not seem to find online.

I have variable letters for example BN and would like a formula that would subtract that value by one so it displays BM.

Any suggestions would be much appreciative.

 

[Fluff]

Probably a simpler way than this

Excel 2013/2016
	A	B
1	BN	BM
Cover

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A1&"1"))-1,2),"$",REPT(" ",100)),100))

 

[shg]

A minor simplification and a UDF alternative:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Column​

[/td][td="bgcolor:#F3F3F3"]

Prior Col​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]BN[/td][td]BM[/td][td]B2: =SUBSTITUTE(ADDRESS(1, COLUMN(INDIRECT(A2 & 1)) - 1, 4), 1,)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]BN[/td][td]BM[/td][td]B3: =ColLtr(ColNum(A3) - 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]ABCDEFG[/td][td]ABCDEFF[/td][td][/td][/tr]
[/table]

Function ColLtr(ByVal iCol As Long) As String
    ' shg 2012
    ' Good for any positive Long
    If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function

Function ColNum(ByVal sCol As String) As Long
    ' shg 2012
    ' Good to column FXSHRXW (2147483647)
    If Len(sCol) > 0 And _
       (Len(sCol) < 7 Or UCase(sCol) <= "FXSHRXW") Then
        ColNum = Asc(UCase(Right(sCol, 1))) - 64 _
                 + 26 * ColNum(Left(sCol, Len(sCol) - 1))
    End If
End Function

 

[jjasmith4]

=SUBSTITUTE(SUBSTITUTE(CELL("address",OFFSET(INDIRECT(A1&"1"),0,-1)),"$",""),"1","")

It appends "1" to the column letter to get a valid cell address, then it uses INDIRECT to get that cell, then OFFSET to refer to one column left (-1), then CELL/"address" to get that cell's address string. Since the string has dollar signs and a 1, it strips those with SUBSTITUTES.

 

[Dojorgen]

man you guys are great thanks a bunch

 

[GR00007]

You could also try
=LEFT(A2,LEN(A2) -1) & CHAR(CODE(RIGHT(A2,1)) -1)

 

[jjasmith4]

How about BA - 1 => AZ?