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

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)

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.