Search code examples
google-sheetsarray-formulas

Google Sheets function Address() results in adding unwanted characters ''! when used in ArrayFormula

If you look at the following table in Column A I have some values that I want to use to make an address reference. I can successfully accomplish this with the formula in Column C copied down into each cell. I would like to use the more dynamic ArrayFormula so that when even I enter a new row it will populate with the correct Address. However, it's injecting ''! before my addresses. I don't know what to do to get rid of it.

I have a workaround in another column in row 3 I enter this. =ARRAYFORMULA(IF(LEN(B3:B), MID(B3:B,4,5),)) however I feel the address should just work without it. Anyone know why it does the unnecessary injection ''!.

link to example Sheet 

+------------+---------------------------------------------------------------+-----------------------------+
|            | Incorrect but dynamic                                         | Correct if but not dynamic  |  
+------------+---------------------------------------------------------------+-----------------------------+
| Formula -> | ARRAYFORMULA(IF(LEN(A3:A), ADDRESS(Row(A3:A),A3:A,4,TRUE,),)) | ADDRESS(Row(A3),A3,4,TRUE,) |
|    1       | ''!A3                                                         | A3                          |
|    2       | ''!B4                                                         | B4                          |
|    3       | ''!C5                                                         | C5                          |
|    4       | ''!D6                                                         | D6                          |
|    5       | ''!E7                                                         | E7                          |
|    1       | ''!A8                                                         | A8                          |
+------------+---------------------------------------------------------------+-----------------------------+
Solution 
  • =ARRAYFORMULA(IF(LEN(A3:A),SUBSTITUTE(ADDRESS(ROW(A3:A),A3:A,4,1,),"''!",""),))

    5