Formula that take number at the end of text

Occasional Visitor

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!

1 Reply

@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))