Create more than one Hyperlink in formula field

Question

I have a custom field which is holding space separated values like: "India USA Australia Japan China". Now I want to make each value hyperlink separately in formula field something like : Hypelink(India) Hyperlink(USA) ................ Is there any way I can do this is formula field or do I need to go with coding only?

Regards, Bhawani Sharma

Answer 1

Formulas do not have a iterator function or even variables, so for an arbitrarily-limited space-delimited list of values, you'd find it difficult to do so with a formula. You can certainly emit multiple HYPERLINK functions in a single formula, but the number of HYPERLINK values must be fixed or it would quickly become unmanageable. Just to support 0-2 parameters would look like:

IF(FIND(Field, " ") > 0,
    IF(FIND(Field, " ", 1+FIND(Field, " ")) > 0,
    HYPERLINK(LEFT(Field, FIND(Field, " ")), "Value 1") & HYPERLINK(MID(Field, FIND(Field, FIND(Field, " "))), "Value 2"),
    HYPERLINK(Field, "Value 1")),
"")

Each additional space would complicate the formula considerably. Given your example, you'd have five nested IF statements with deeply nested FIND statements that would be nearly impossible to maintain. You'd be better of writing Apex Code and writing the results to a Rich Text Field.