the 1st ref in OFFSET tells excel where to start from (where the offset starts from)
then how many rows to go down
then how many columns to go down.

If you have A5 as the reference you want to get info from, then you need to specify how rows to go down and how many columns to go across...
=OFFSET(Star-Ref,rows, columns)

You generally use values for the rows and column arguments, but a cell-ref can be used as well...
A
B
1
 b5 abc
2
 cba
3
4
5
 abc cba


B1=OFFSET(A1,RIGHT(A1,1)-1,0) This would be used when the column is the same (I changed it to B for the next example)
B2=OFFSET(A1,RIGHT(A1,1)-1,CODE(LEFT(A1))-97)

However, this seems a pretty long way around getting contents of a cell in a known location? Perhaps consider INDIRECT()?
=INDIRECT(A1) = cba