Offset cell addresses

[ChristineJ]

Cell T31 = E9, X5, A1

I am using the following formula in cell U31 to add two column letters and one row to each cell reference in T31:

=IF(T31="","",SUBSTITUTE(TEXTJOIN(", ",1,@CELL("address",OFFSET(INDIRECT(TRIM(MID(SUBSTITUTE(T31,", ",REPT(" ",LEN(T31))),@SEQUENCE(LEN(T31)-LEN(SUBSTITUTE(T31,",",""))+1,,0,LEN(T31))+1,LEN(T31)))),1,2))),"$",""))

It returns G10 in cell U31 - it only works for the first cell address, E9.

Can this be adjusted so that it returns G10, Z6, C2 in cell U31 --- offsetting all three cell addresses? Thanks

 

[Eric W]

How about:

Book1
	T	U
31	E9, X5, A1	G10, Z6, C2
Sheet1

Cell Formulas
Range	Formula
U31	U31	=IF(T31="","",LET(ad,TRIM(MID(SUBSTITUTE(T31,",",REPT(" ",99)),SEQUENCE(LEN(T31)-LEN(SUBSTITUTE(T31,",",""))+1,,,99),99)),TEXTJOIN(", ",1,ADDRESS(ROW(INDIRECT(ad))+1,COLUMN(INDIRECT(ad))+2,4))))

 

[jasonb75]

You could use the vba that you asked for earlier as a function to make the formula much simpler.

Function MCELL(r As Long, c As Long, rng As String) As String
MCELL = Range(rng).Offset(r, c).Address(0, 0)
End Function

Book1
	T	U
31	E9, X5, A1	G10,Z6,C2
Sheet4

Cell Formulas
Range	Formula
U31	U31	=mcell(1,2,T31)

 

[ChristineJ]

How about:

Book1
	T	U
31	E9, X5, A1	G10, Z6, C2
Sheet1

Cell Formulas
Range	Formula
U31	U31	=IF(T31="","",LET(ad,TRIM(MID(SUBSTITUTE(T31,",",REPT(" ",99)),SEQUENCE(LEN(T31)-LEN(SUBSTITUTE(T31,",",""))+1,,,99),99)),TEXTJOIN(", ",1,ADDRESS(ROW(INDIRECT(ad))+1,COLUMN(INDIRECT(ad))+2,4))))

Yes, thank you! CJ

 

[ChristineJ]

You could use the vba that you asked for earlier as a function to make the formula much simpler.

Function MCELL(r As Long, c As Long, rng As String) As String
MCELL = Range(rng).Offset(r, c).Address(0, 0)
End Function

Book1
	T	U
31	E9, X5, A1	G10,Z6,C2
Sheet4

Cell Formulas
Range	Formula
U31	U31	=mcell(1,2,T31)

Brilliant! Thank you. I did not even realize I was asking the same thing in a different way. Appreciate your help on this.