Alphanumeric number parse to just the alpha

dgloballab

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!

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.