Help

#ERROR! on very simple SUBSTITUTE

Topic Labels: Formulas
Solved
Jump to Solution
508 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions

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} & "")

See Solution in Thread

3 Replies 3

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.

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} & "")

Ahhh, perfect. Worked.

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