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:

	A​	B​	C​
1​	Column​	Prior Col​	​
2​	BN	BM	B2: =SUBSTITUTE(ADDRESS(1, COLUMN(INDIRECT(A2 & 1)) - 1, 4), 1,)
3​	BN	BM	B3: =ColLtr(ColNum(A3) - 1)
4​	ABCDEFG	ABCDEFF

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?