I have this table (shown below) in which there are two select lists with numbers and text. Is there a formula that will extract just the number from those rows and sum for a total score as shown below? I have tried sumif, countif, ifnumber and several others with no luck. Thanks in advance for any help!
If I understand correctly what you are trying to do is similar to a slice (First, Last, Nth, and Slice | Coda Help Center)
Yes but the issue im having with that is it will only pull the whole line and not just extract the number since the numbers aren’t coma separated.
Okay, but wouldn’t it be possible to replace the separator with a replace(, , , )?
Alas with my small level, I can’t provide you more help but I continue the research and if I find a solution, I will come back to share it with you if nobody answered before
PS: I’m also thinking of First() and Last() maybe see on this side too?
@Thierryvm thank you so much for your input.
I’m sorry I wasn’t more helpful
@Thierryvm any input is helpful and I had not seen that article you sent so I am definitely better for that. Thanks again!
Hey @Terry_Stagg. Here’s what I think the issue is…
The value of the SELECT columns are strings (ie, Text), so you need to force them to be numbers if you want to use them as such.
Assuming the format of the options is consistent (number, followed by a hyphen, followed by the text description), then there are a number of ways to extract just the number out of that string. The one that comes to mind first, and is reasonably legible, would probably be to
Split() the value by a hyphen delimiter, then select the
First() item from the resulting list.
That’d return “1” but (and here’s why I think you’re getting that “wrong argument type” error), that “1” is still a string (text). To work with it as a number, you need to use
.ToNumber(). That casts the variable into a number, and then you can work with it mathematically.
So the full formula for “Total Score” would be:
If that doesn’t work, let me know and we’ll work on it together!
@Jono_Bouwmeester as always you saved the day! That worked perfectly and yields exactly the results I was needing. Thanks so much for your help!!
Absolute pleasure, @Terry_Stagg. Glad it worked!
A similar one that comes up all the time in CFL is referring to a lookup or some other object then trying to work with it like it’s text. It’s become a default in my head to pretty much always throw a
.ToText() in whenever I’m about to work with some text.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.