Alphanumeric number parse to just the alpha

I have a column with an alphanumeric number "CYG2525" or it could be "C2525", "GY2525". As you can see the alpha can range in the amount.

I need to be able to pull over just the letters so "CYG" or "C" or "GY" for example. I thought I could do this with saying give me everything left of the "2" but the issue would be when there is more than one "2" in the number.

Context

Alpha = Region

Number = Identifier

We have updated our form for any new submission to pull into these new cells with a drop down for Region and a number only field for identifier so we are good with going forward just need to convert all our existing data.

Thanks for the help!

Find more posts tagged with

Accepted answers

All comments

dgloballab

I should add the alpha can range from one letter to max of five.

dgloballab

can someone please look at this one?

dgloballab

Could someone vote up my first comment. i wonder if that will get it some movement?

jodyh

@dgloballab is it always four numeric digits? If so, you can use a RIGHT function to extract the numeric piece -

=RIGHT([Original Designation]@row, 4)

I'm new to this, but was playing around with this one...I'll let you know if I get anymore traction with the alpha piece.

dgloballab

@jodyh I did get the numeric part figured out just fine. Its getting the letters from the beginning that I am struggling with. Thanks for response though.

jodyh

For the alpha piece, you could do a nested if(contains) formula-

=IF(CONTAINS("CYG", [Original Designation]@row), "CYG", IF(CONTAINS("C", [Original Designation]@row), "C", IF(CONTAINS("GY", [Original Designation]@row), "GY", " ")))

Of course, if you have a lot of regions, this could get ugly- I'm just not sure how/what you're working with.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.