Parse text, include variable, compute it

Hi All,

I have zero knowledge in VBA, tried to find a solution to my problem with functions, unfortunately without success.

I have an Excel sheet that contains text values, for example:
Run for 5 mins at < 70% of a VAR> pace
It could also contain < VMA70> < VMA/0.7> or < VMA>/0.7, but I guess that it's just a matter of "replace all".

I would like to replace “< VAR>” by the content of a cell and compute it, for example above multiplied by 70%.
Sometimes such a tag will even appear twice in 1 cell, and there might be carriage returns in cells.

Problem is that the cell is text. I believe I actually need to:

First replace the cell by something like:
=”Run for 5 mins at “&< 70% of a VAR>&” pace”

Then probably replace < 70% of a VAR> by < VAR>*70% to get:
=”Run for 5 mins at “&< VAR>*70%&” pace”

To finally replace < VAR> by a certain cell, let’s say H4, and force its evaluation:
=”Run for 5 mins at “&H4*70%&” pace”

If H4="05:10" the final text should then be ”Run for 5 mins at 07:23 pace”

Note that I will also have to encode this in "mm:ss", so I will need something like TEXT(myresult,"mm:ss"), but I believe that won't be too much of an issue.

Could you please help me understand if my reasoning is correct, and how I could proceed with such replacements to fix about many cells in 1 go?
I will have about 50 cells to do in about 40 Excel tabs or files.

Enclosed is my file for reference.

Thanks a lot for your help as always!

Thanks a lot for the reply dflak!

I confirm that the values should be calculated, so showing «*2*» and not «*10/5*».
In my example from initial post, if I calculate 70%:
If H4="05:10" the final text should then be ”Run for 5 mins at 07:23 pace”

Thanks again!

Your explanation is not clear to me.

If you upload a workbook with your exact desire result, it would help us to understand what you are trying to do.

Hi Jindon, thanks for your answer.
Apologies, I thought it was attached but it actually wasn't.
Enclosed is a sample file with first column containing the "raw" data and the second column with computed/expected results (done manually).
I wish to be able to automatically generate the second column for all 40+ cells of the first column, and for about 50 Excel tabs....

To explain my logic:
All is based on cell H4 = 00:05:10 (5 min and 10 s). I need to derive percentages of this value in many calls, expressed like < VMA100> or as another example < VMA100>/5.
< VMA100> means 100% of my variable H4. < VMA70> would mean 70% of that H4.
2 examples, that I did manually:
Footing 45 mins à < VMA70> min/km
Should become
Footing 45 mins à 07:23 min/km

"Warm up 20 mins
2x(8x200m+< VMA100>/5)+02:00 à < VMA100> min/km
Cool down 15 mins"

Should become:
"Warm up 20 mins
2x(8x200m+01:02)+02:00 à 05:10 min/km
Cool down 15 mins"

Thanks a lot,
G

It is /0.7 I swear! :-)

Then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks a lot guys, I will test tonight and will let you know the outcome!

Hi All,

Sorry for the late feedback on this.
Unfortunately the macro doesn't seem to work yet.
I do see data copied from column D to column E, but my <*VMA> values that need to be calculated just remain <*VMA>

One question for clarity, because I can't read the REGEXP well. If I want to compute (for example) "divided by 0.7", does the REGEXP expect <*VMA/0.7> or <*VMA>/0.7 (operation inside or outside the tag)?
I can easily clean up all regexp to move the calucation inside or outside the tags, but at the moment none of the 2 get translated.

Enclosed is my Excel sheet with macro included, would you be so kind to help me understand why REGEXP is not replaced?

thanks again so much,
G