#ERROR! on very simple SUBSTITUTE

[Tobias_LGKR]

Hi,

I am getting an Error back on the very basic SUBSTITUTE, which I have used numerous times in other tables of my base.

SUBSTITUTE({Record Name}," “,”-")

{Record Name} is a FORMULA FIELD that pulls the text from a LOOKUP FIELD (in the same table) that pulls the text from a FORMULA FIELD in another table.

Any ideas? Thanks in advance.

Tobias

[Tobias_LGKR]

UPDATE:

This formula also produces an error:

LOWER({Record Name})

However, if I only reference {Record Name} in the formula the error goes away and my desired text appears.

[kuovonne · Accepted solution]

Lookup fields are the trickiest to use in formula fields. This is because they often (but not always) return arrays, not simple text strings or numbers.

Try these versions that convert the array to a string by using & ""

SUBSTITUTE({Record Name} & ""," ","-")

LOWER({Record Name} & "")

[Tobias_LGKR]

Ahhh, perfect. Worked.

As you said. I have referenced Lookup Fields before without any problem.