Forum Discussion
extracting number from text string and doing calculations
I have to extract the left most amount and the number of days from the below attached text string and perform mathematical operations depending on the number of days. Condition $280 for d...
I like the RegEx approach and would identify it as having the greatest potential for extension to more complex cases.
Despite that I went ahead with a formula-based approach using worksheet functions and named formulas. Picking up the notation implicit in the instructions in cell C2, I defined 'number' and 'amount' to be
= MID(Condition, 2, SEARCH(" ", Condition) - 2) and
= VALUE( MID(Condition, SEARCH(",", Condition) - 1, 1 ) )
respectively. Having tucked the messy steps out of sight, the formula is
= amount * IF(number<4, number, 4)
I, naturally, did this as a dynamic array formula but it is also possible to use CSE formulas or even relative referencing (given an appropriate definition of 'Condition') .