# 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

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

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

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.