Excel: include formatting in formula of another cell

Question

I have a spreadsheet which contains a hyperlink based on the contents of another cell. I need the hyperlink to be based on the display (formatted) value rather than the data value of the source cell. Is this possible without resorting to VBA? Example:

Actual values:

| A |          B                | 
| 1 | =HYPERLINK("../Form_"&A6) |
| 2 | =HYPERLINK("../Form_"&A7) |
| 3 | =HYPERLINK("../Form_"&A8) |

Displayed As:

Column A custom format: "Form_"000

|    A     |      B      | 
| Form_001 | ../Form_001 |
| Form_002 | ../Form_002 |
| Form_003 | ../Form_003 |

Functional Value:

| A |          B          | 
| 1 | file:///../Form_001 |
| 2 | file:///../Form_002 |
| 3 | file:///../Form_003 |

Answer 1

Is there some fixed format to the contents of Column A and the form names, or is this a generic question?

You could do something using TEXT() if you can avoid the "m" in "Form" (because TEXT() will think it's a month indicator), but that's not a general solution.

For example: =HYPERLINK("../"&TEXT(A2,"For_000#")) works, but =HYPERLINK("../"&TEXT(A2,"Form_000#")) doesn't.

However if you can add the extra bits you need outside of the &text construct you can use whatever characters you like:

=HYPERLINK("../Form_"&TEXT(A2,"000"))

You can use the standard formatting codes in the "..." part, e.g. "$0.00", "yyyy/mm/dd".

Answer 2

I don't see a function for returning a cell as its formatted value. Even though you prefer a non-VBA solution, it is a simple, straight forward function:

Function AsFormatted(v As Variant) As String
  AsFormatted = v.Text
End Function