Have a column with different values and trying to create helper column to assign number

Jason F

Have a column where freeform module numbers are entered (i.e. 101, 204, 300, S811). I want to create a helper column, where it will read the module number and assign a single digit.

(i.e. 101, 104, 141, will all return "1" in the helper column and so on)

Paul Newcome

I would use this:

=IFERROR(VALUE(LEFT([Column Name]@row)), VALUE(MID([Column Name]@row, 2, 1)))

It pulls the first character in and converts it to a number. If that throws an error because the first character is a letter, it pulls the second character and converts to a number.

prime_nathaniel

@Jason F is the only odd one out the S811 with S as a prefix? This is really a better use case for regex than formulas. Basically if the only odd pattern is Letter first then number you can do a check on 1st character if it is a number, if it is grab 1st, if not grab second.

Jason F

All the values which have a letter prefix also have unique number ranges which are specific to it. Is it possible to enter a numeric range, say 100-199 and then any number within that range, returns a 1?

Paul Newcome

I would use this:

=IFERROR(VALUE(LEFT([Column Name]@row)), VALUE(MID([Column Name]@row, 2, 1)))

It pulls the first character in and converts it to a number. If that throws an error because the first character is a letter, it pulls the second character and converts to a number.

Jason F

Thank you Paul!

Paul Newcome

Happy to help. 👍️