Extract a number from a select list

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!

@Terry_Stagg Hello,

If I understand correctly what you are trying to do is similar to a slice (First, Last, Nth, and Slice | Coda Help Center)

Sincerely,
Thierry

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(, , , )?

Sincerely,
Thierry

Every time I get this

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 :slight_smile:

PS: I’m also thinking of First() and Last() maybe see on this side too?

Sincerely,
Thierry

1 Like

@Thierryvm thank you so much for your input.

I’m sorry I wasn’t more helpful :frowning:

Sincerely,
Thierry

@Thierryvm any input is helpful and I had not seen that article you sent so I am definitely better for that. Thanks again!

1 Like

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.

thisRow.[Severity Rating].Split("-").First()

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:

thisRow.[Severity Rating].Split("-").First().ToNumber()
+
thisRow.[Probability…].Split("-").First().ToNumber()

If that doesn’t work, let me know and we’ll work on it together!

3 Likes

@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!!

1 Like

Absolute pleasure, @Terry_Stagg. Glad it worked!

The Coda Formula Language (CFL) is quite similar to JavaScript, which is what’s called a “loosely typed language”. What that means is that the developer doesn’t have to expressly define the type of variable they’re working with and instead relies on the language to “guess” the type as it plods along. Loosely typed languages are easier to work with, and throw fewer errors, but every now and then you run into bugs like this where you think you’re working with an expected type, but you’re actually not. Notoriously tough to troubleshoot, but once you’ve run into it a couple hundred times, you get an eye for it :sweat_smile:

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.

4 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.