Not Getting Correct Result using =Substitute

[RapchikM]

Hello

This is my text in Cell B21
Against completion of all GOVT. approval and submission of security for a period of 1 Year 5%

i used the following Formula in C21
=SUBSTITUTE(RIGHT(B21,COUNT(MID(B21,SEQUENCE(LEN(B21)),2)+0)),"%","",1)

Also tried
=SUBSTITUTE(RIGHT(B21,COUNT(MID(B21,SEQUENCE(LEN(B21)),2)+0)),"%","",2)

and
=SUBSTITUTE(RIGHT(B21,COUNT(MID(B21,SEQUENCE(LEN(B21)),2)+0)),"%","")


I get result as r 5. I dont understand Why ?


Can someone help me to remove r and Space before 5 it should display 5 only

Thanks
RapchikM

 

[Fluff]

You're welcome & thanks for the feedback.