SUB-STI-TUTE

[ZOZOZO]

I'm trying to substitute a number for a value on an enum lis.  The list currently contains the options, 'Good', 'Average', 'Needs Work'.  I'd like the worksheet to receive a '1' for Good, '2' for Average and '3' for Needs Work.  However I'm lost.  I'm trying to use SUBSTITUTE  but I receive an error saying I'm using it incorrectly.  Any help is appreciated.

[dbaum]

You may need to first convert the overall list into a single text value (using the CONCATENATE or TEXT functions), perform the SUBSTITUTE function on that text value, and then re-parse the result into a list (using the SPLIT function).

[aneeshatgoogle]

Since your use case is to replate the entire text string rather than just part of it, SUBSTITUTE method will not be the most optimal solution due to performance or and data validation.

SUBSTITUTE method tries to find different occurrences which is slower than matching the whole string. A SWITCH() might be a better expression for your use case:
https://support.google.com/appsheet/answer/10107700

This has better performance, and you can set another number (for eg. -1) as default to identify invalid enum values in the sheet.

[ZOZOZO]

In the field that contains an enum, I've added the formula below. Though
the expression assistant allows the code, the enum field disappears from
the view. What am I missing?

Thanks in advance.

SWITCH([Lateral Cervical Flexion],
"Good", "1","Average", "2",
"N/A"
)