Receiving #invalid value output from a formula

jason.smith2

I have a fairly simple ask and I originally built the formula myself, but since the output was an error I ran it through Smartsheet formula generator and our internal company AI. Both gave the same formula.

I have a column with Client Names that can begin with either a letter or number. I have a column that needs to output the first letter or number of the client name. If the value is a number then I want to store that as such, and a letter as text.

This is the formula that is giving me an #invalid value when the client name begins with a letter. If it is a number, the output is accurate and is stored as a numerical value.

=IF(ISNUMBER(VALUE(LEFT(Client@row, 1))), VALUE(LEFT(Client@row, 1)), LEFT(Client@row, 1))

I did add an IFERROR to the front of the formula and ended with LEFT(Client@row, 1) so I get the letter outupt from the client name, but I would like to understand why the original formula would not work if anyone can provide insight.

Thank you!

jessica.smith

@jason.smith2

Remove the first VALUE function. It's converting the first character to a value so when you try to return it as text it is invalid.

=IF(ISNUMBER(LEFT(Client@row, 1)), VALUE(LEFT(Client@row, 1)), LEFT(Client@row, 1))

jason.smith2

Thanks so much!

Completely overlooked that.