Excel: Get range address from single input (looking for compact formula)

This would be another way:

=CELL("address",A1:B10)&":"&CELL("address",INDEX(A1:B10,ROWS(A1:B10),COLUMNS(A1:B10)))

And if you have Office 365 use LET to reference the range only once

=LET(ref, A1:B10, CELL("address",ref)&":"&CELL("address",INDEX(ref,ROWS(ref),COLUMNS(ref)))

Consider the user defined function:

Option Explicit

Public Function adres(rng As Range) As String
    adres = rng.Address(True, True)
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from an Excel cell:

=adres(A1:B10)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Actually, your attempt #1 is exactly the way to do it. If you are looking for a way to make it appear more like a function (less repeats of the reference/ single use of the "argument" address), the best way is using LAMBDA in Office 365. With LAMDA, you can provide arguments to a named range, defining MAGIC as

=LAMBDA(ref, ADDRESS(MIN(ROW(ref)),MIN(COLUMN(ref)),1,1)&":"&ADDRESS(MAX(ROW(ref)),MAX(COLUMN(ref)),1,1)}

then using =MAGIC(A1:B10) in the worksheet.