Unless I am misunderstanding things, you simply want to change the simple 1-3 digit number value in column A to be preceded by "Form_" and to always use three digits to present the number.
The formatting string that will do that is:
\F\o\r\m_000
Note the "\" before each of the first five characters. That ensures Excel treats them as text, not as something like a month indicator, or some other bizarre surprise. Then the three zeros at the end will be understood as requiring a number to have the full three characters, so "82" becomes "082" and so on.
But of course, formatting the cells to have that format won't help you get that output to use in a text string! However, you can use the TEXT()
function WITH that string specified to do exactly that. Then your HYPERLINK()
function will have all the string material it requires to form the desired hyperlink.
Please note that:
"Form_"000
will also work in cell formating as the double quotes around the text makes Excel realize not to see, sasy, the "m" as indicating a month, but in the TEXT()
formula, would need doubled double quotes where you see the single double quotes now:
""Form_""000
because the formula itself is seeing the single double quotes as setting off the entire formatting string rather than indicating text to put in the format. But I find the extra double quoting obnoxious and occasionally bizarre in what ends up working so I would use the first formatting string I gave since it will always work and requires nothing more obnoxious... oh... than all those \'s.
Hope that's what you were really looking for!
=HYPERLINK("X:/Path/to/Form_"&TEXT(A2,"000")
. Post as an answer and get your just dues :)