Formula that take number at the end of text

%3CLINGO-SUB%20id%3D%22lingo-sub-3461158%22%20slang%3D%22en-US%22%3EFormula%20that%20take%20number%20at%20the%20end%20of%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3461158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20SharePoint%20list%20that%20I%20want%20to%20do%20a%20formula%20for%20one%20of%20the%20columns.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20column%20of%20%22bac_id%22%20and%20a%20column%20of%20%22number%22.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20the%20number%20to%20be%20the%20end%20of%20the%20back_id.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3Efor%20example%3C%2FSTRONG%3E%3A%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20bac_id%20is%20%22rr48%22%20the%20number%20box%20will%20be%20%2248%22%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20bac_id%20is%20%22Shf12%22%20the%20number%20box%20will%20be%20%2212%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20bac_id%20is%20%22Ds121%22%20the%20number%20box%20will%20be%20%22121%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ethanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3461158%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3466351%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20that%20take%20number%20at%20the%20end%20of%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1411810%22%20target%3D%22_blank%22%3E%40Razrosa%3C%2FA%3E%26nbsp%3BThat%20was%20tricky!%26nbsp%3B%20Here%20is%20my%20assumption%20based%20on%20your%20example%20-%20any%202-3%20characters%20followed%20by%20any%202-3%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20how%20it%20works%20in%20case%20you%20need%20to%20modify%3A%3C%2FP%3E%3CP%3EIf%20(condition%2C%20true%2C%20false)%3C%2FP%3E%3CP%3ECondition%3A%26nbsp%3B%20RIGHT%20gets%20last%20three%20from%20bac_id%2C%20VALUE%20check%20to%20see%20if%20it's%20a%20number%20and%20returns%20and%20error%20if%20it's%20not%20valid%2C%26nbsp%3B%20ISERROR%20will%20return%20true%20or%20false.%26nbsp%3B%3C%2FP%3E%3CP%3Etrue%3A%26nbsp%3B%20If%20there%20is%20an%20error%2C%20then%20there%20was%20a%20letter%2C%20use%20only%20get%20the%20last%202%20from%20bac_id%3C%2FP%3E%3CP%3Efalse%3A%20if%20there%20is%20now%20error%2C%20then%20use%20the%20last%203.%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(ISERROR(VALUE(RIGHT(%5Bbac_id%5D%2C3)))%2CRIGHT(%5Bbac_id%5D%2C2)%2CRIGHT(%5Bbac_id%5D%2C3))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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))