Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

#ERROR! on very simple SUBSTITUTE

Topic Labels: Formulas
Solved
Jump to Solution
1946 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Tobias_LGKR
7 - App Architect
7 - App Architect

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
kuovonne
18 - Pluto
18 - Pluto

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
Tobias_LGKR
7 - App Architect
7 - App Architect

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
18 - Pluto
18 - Pluto

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.