Forum Discussion
extracting number from text string and doing calculations
Hi,
Please try this formula in cell B2 and then drag it down:
=IF(MID(A2,SEARCH("gh",A2)+3,SEARCH(",",A2)-(SEARCH("gh",A2)+3))+0>=4,(LEFT(A2,SEARCH(" ",A2))+0)*4,(LEFT(A2,SEARCH(" ",A2))+0)*(MID(A2,SEARCH("gh",A2)+3,SEARCH(",",A2)-(SEARCH("gh",A2)+3))+0))
Hope that helps
It doesn't seem to work, i guess we have to remove the $ symbol and also convert the text to number.
Both the solutions work on the sample data you provided.
How are the not working? Can you elaborate it a bit more? Both the solutions take care of the $ sign in the beginning of the strings.
Can you provide few more strings you are having trouble with?
Thanks for this awesome function. Can you please explain to me the functionality of it as i want to write similar functions for one of my projects.
Thanks again 🙂
I used Regular Expression which is a special text string for describing a search pattern to extract the numbers we are interested in.
If you go to https://regex101.com/r/XItM9Q/1, it shows you how does it work.
I used the search pattern as ^\$(\d+) for days 1 through (\d+).
The special symbols used in the search pattern has the following meaning...
^ --> Search from the beginning of the string.
\$ --> Literal character $
\d --> Match a digit
( ) --> used for groupings i.e. if the pattern matches anything, the string returned will have two groups.
+ --> Repeats the previous pattern one or more times, in this case one or more digits.
So basically I am asking the RegEx engine to return me two sets of numbers in two different groups if the number in the first group is preceded by a $ sign and followed by a string " for days 1 through " which is then followed by another number.
And if the RegEx engine finds any matching string based on the search pattern, we fetch both the numbers in two variables called Amount and Days and do the calculation based on the criteria.
You may also find the following image helpful.
