Receiving #invalid value output from a formula
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!
Answers

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))

Thanks so much!
Completely overlooked that.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!