How to add numbers in text in a cell?
I have an Excel sheet 
It is required to sum the numbers in the cells in column "C" and place the value in the corresponding cell in column "B" (in my example, 0.25 + 1 + 0.25 + 1 = 2.5).
The values in column "C" are subject to change. You want to update the value in "B" as soon as the cell in column "C" changes.
I asked the author to change the text and numbers in two different columns, but this is not acceptable due to the standard pattern.
source to share
This can be achieved without changing the text in the cell using a macro. I have to admit that the implementation will be a bit backward as you have a few things to do.
1 - Designate all cell content and store the tokens into a string array.
The problem is already there. The text inside the cell is grossly contradictory. You have -250ms, - 250ms, - 1sec, - 1sec. Due to this, you will need to check each instance. So you can't just check if the value is a number, you need to check all the permutations that your team members have provided you with.
The best way to do this is by checking regular expressions. Hence # 2 should be:
2 - Check each value to see if it matches one of the four regular expressions.
3 - Depending on the regex it matches, extract the numeric value.
a) If just a value (i.e. → 250), check if the value exceeds ms or sec. IF ms, divide the number by 1000. If sec, leave the number as it is. Add number to total. b) If the number is preceded by a dash (-), remove the dash and repeat step a)
c) If the number followed by letters, remove the letters and repeat step a)
d) If the number followed by a period, do nothing. It's a bullet.
source to share