Forum Discussion

Razrosa's avatar
Razrosa
Copper Contributor
Jun 02, 2022

Formula that take number at the end of text

Hello,

I have a SharePoint list that I want to do a formula for one of the columns.

I have a column of "bac_id" and a column of "number".

I want the number to be the end of the back_id.

for example:

if bac_id is "rr48" the number box will be "48"

if bac_id is "Shf12" the number box will be "12"

if bac_id is "Ds121" the number box will be "121"

thanks!

SharePoint Online
  • PamDeGraffenreid's avatar
    PamDeGraffenreid
    MVP
    Jun 03, 2022

    Razrosa That was tricky!  Here is my assumption based on your example - any 2-3 characters followed by any 2-3 numbers.

     

    Here's how it works in case you need to modify:

    If (condition, true, false)

    Condition:  RIGHT gets last three from bac_id, VALUE check to see if it's a number and returns and error if it's not valid,  ISERROR will return true or false. 

    true:  If there is an error, then there was a letter, use only get the last 2 from bac_id

    false: if there is now error, then use the last 3. 

    =IF(ISERROR(VALUE(RIGHT([bac_id],3))),RIGHT([bac_id],2),RIGHT([bac_id],3))

     

Resources