Excel Loops to find Integers in a Sequence

[jwenzCHUBB]

Hello Helpful People,

I need to apply an excel formula to a string of data. 

Here is a screenshot of what I am working with: 

In the sheet, Cell B2 will be automatically input as well as cell B4 in the exact format shown above.

I am hoping to create a formula that produces what is seen in cell B6 as opposed to doing the percentage calculations and typing it out every time. 

I need to loop through B4, find the different integers that are separated by the $m/ characters and then be able to do the simple division and concatenate the text as seen in B6.

Anyway this is possible will be greatly appreciated and save me tons of time.

Thanks so much!

-JW

[#2]

@jwenzCHUBB 

It is possible. But when I see something like this I ask myself "WHY?!". Why not split the data over three columns, put in some basic formulas and your ready?

[#3]

Yes, very good point. HOWEVER the data is being populated into the cells via a Microsoft Forms entry and therefore will show up as this string in the cell B4.

I used power automate and it's being sent to Planner Tasks and if I just had 5 10 15, the formatting would be off.

HOWEVER now that I'm typing this, maybe I will just input the "$" "m" into the planner task and do what you're saying on individual form entries....

Thanks for the new perspective haha.

I'll work on that but if anyone does want to provide how to do it, that'd be a cool learning experience.

[#4]

@jwenzCHUBB 

This might work:

=LET(s,SEQUENCE(LEN(B4)-3),u,UNIQUE(FIND("$",B4,s))+1,m,UNIQUE(FIND("m",B4,s)),d,m-u,p,MID(B4,u,d),per,TEXT(p/B2,"(0%)"),TEXTJOIN("/",1,DOLLAR(p,0)&"m "&per))