Adding "remove non-numerals" to my fabulous formula?

[jmpatrick]

Happy Halloween!

I have this not-so-spooky formula that takes the value of Column C and returns everything to the right of the dash:

=RIGHT(C785,LEN(C785)-SEARCH("$",SUBSTITUTE(C785,"-","$",LEN(C785)-LEN(SUBSTITUTE(C785,"-","")))))

7073M-5B becomes 5B. How do I remove all non-numerals so it returns just 5?

jp

 

[hagia_sofia]

Hello, one way could be:

=--(TEXTJOIN("",1,TEXTSPLIT(TEXTAFTER(C785,"-",-1),CHAR(VSTACK(SEQUENCE(47,,1),SEQUENCE(198,,58))))))

 

[jmpatrick]

Hello, one way could be:

=--(TEXTJOIN("",1,TEXTSPLIT(TEXTAFTER(C785,"-",-1),CHAR(VSTACK(SEQUENCE(47,,1),SEQUENCE(198,,58))))))

It works on that row but if I copy it down it breaks.

 

[hagia_sofia]

Could you please possibly post some data where it breaks?

 

[KRice]

Another option along with that offered by @hagia_sofia . I don't see any issues with copying the formula down.

MrExcel_20241030.xlsx
	A	B	C
3	70-73M-5B6	56	56
4	70-73M-75B6	756	756
5	70-73M-89	89	89
Sheet3

Cell Formulas
Range	Formula
B3:B5	B3	=LET(t,TEXTAFTER(A3,"-",-1),TEXTJOIN("",1,IFERROR(MID(t,SEQUENCE(LEN(t)),1)+0,""))+0)
C3:C5	C3	=--(TEXTJOIN("",1,TEXTSPLIT(TEXTAFTER(A3,"-",-1),CHAR(VSTACK(SEQUENCE(47,,1),SEQUENCE(198,,58))))))

 

[jmpatrick]

Another option along with that offered by @hagia_sofia . I don't see any issues with copying the formula down.

MrExcel_20241030.xlsx
	A	B	C
3	70-73M-5B6	56	56
4	70-73M-75B6	756	756
5	70-73M-89	89	89
Sheet3

Cell Formulas
Range	Formula
B3:B5	B3	=LET(t,TEXTAFTER(A3,"-",-1),TEXTJOIN("",1,IFERROR(MID(t,SEQUENCE(LEN(t)),1)+0,""))+0)
C3:C5	C3	=--(TEXTJOIN("",1,TEXTSPLIT(TEXTAFTER(A3,"-",-1),CHAR(VSTACK(SEQUENCE(47,,1),SEQUENCE(198,,58))))))

It works! My issue was with values that didn't include a dash. I added an IFERROR that solved it. Thanks to both!