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]

How about

=TEXTAFTER(B21," ",-1)*100

I would also suggest you update your profile as 2013 does not have the sequence function.

 

[RapchikM]

I would also suggest you update your profile as 2013 does not have the sequence function.

Sir, Profile updated

No more now on Excel 2013

In Excel 2021 tried incorporating =TextAfter but does not pop up. Tried But Error Genrated #Name?
So any other Method

Thanks
RapchikM

 

[Fluff]

Profile updated

Thanks for that.

How about

=TRIM(RIGHT(SUBSTITUTE(B21," ",REPT(" ",100)),100))*100

 

[RapchikM]

Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(B21," ",REPT(" ",100)),100))*100

Yes Tried but Result Displays as 500% instead of 5

Tried following

=TRIM(RIGHT(SUBSTITUTE(B21," ",REPT(" ",100)),100))*1

Now displays as 5% instead of 5
RapchikM

 

[Fluff]

You said you wanted it to display 5 which is exactly what that does if you change the cell format to general.

 

[RapchikM]

You said you wanted it to display 5 which is exactly what that does if you change the cell format to general.

Whether the Cell or Column Format when changed to General it displays as 0.05 It is Perfectly Fine for that Cell or Range of column with the Formula what we have incorporated

Moving Ahead Still Multiplying a value from Different Cell as per below

=TRIM(RIGHT(SUBSTITUTE(B15," ",REPT(" ",LEN(B21))),LEN(B21))*1

Thought of Replacing % to "" ---> this too did not work
=C$12 * REPLACE(TRIM(RIGHT(SUBSTITUTE(B21," ",REPT(" ",LEN(B21))),LEN(B21))*1),LEN(B15)-3,2," ")
OR
=C$12 * TRIM(RIGHT(SUBSTITUTE(B15," ",REPT(" ",LEN(B21))),LEN(B21))*1


Value of C12 = 1792350
The Result with above formula it comes as 8961750% instead of 89617.50
ie 5% of 1792350

RapchikM

 

[jdellasala]

As noted, @Fluff's formula performed as originally noted. It can still work, actually returning the TEXT value of "5%" when the ending "*100" is removed from the formula, and when converted using VALUE returns 0.05

=VALUE(TRIM(RIGHT(SUBSTITUTE(B21," ",REPT(" ",100)),100)))

If that's what you need, the solution should go to @Fluff!

 

[Fluff]

Your formula is looking at B15, but using the length of B21.
Try

=C12*RIGHT(SUBSTITUTE(B21," ",REPT(" ",100)),100)

and format the cell as general not percentage

 

[RapchikM]

Your formula is looking at B15, but using the length of B21.

Sorry for Typo Error

Thank you so much sir for your Efforts and Help

RapchikM