I am just baffled about using the value of the lookup field cannot be directly used in a formula

WEIRD SITUATION 1


When you use


{My Email To} << lookup field, with single value

LEN({My Email To}) return #ERROR

Solution 1


LEN(CONCATENATE({My Email To}))

Solution 2


LEN({My Email To}&"")

Freaking amaaaaaaazziiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiing riiiiiiiiiiiiiiiiiiiiiiiiiiiiiiight?

is this the simplest solution? do you have any other solution?

WEIRD SITUATION 2


When you use


{Name} with values (Name1, Name2), << linked field, with multiple values


{My Email To} with values (Email 1, Email2) << lookup field, values shows with space and comma

{My Email To}&"" return values (Email 1Email2) << formula field without space and comma!!!

why does the lookup field value change? why can’t they just make that in-line value a literal flat string?