09-16-2019 08:42 PM
09-16-2019 08:42 PM
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.
|$280 for days 1 through 6, $0 afterwards|
|$240 for days 1 through 3, $0 afterwards|
|$290 for days 1 through 4, $10 afterwards|
|$280 for days 1 through 5, $10 afterwards|
Final sum for all the below rows to be calculated according to logic.
Amount*4 ( If number of days if >=4)
Amount*number of days( If number of days is less than 4)
1) The first sum should be 280*4
2) second sum should be 240*3
09-16-2019 08:59 PM
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
09-16-2019 09:17 PM - edited 09-16-2019 09:32 PM
You may simply try the following User Defined Function. Place the following Function on a Standard Module like Module1 and then use it on the Worksheet just like a regular Excel Formula.
Function CustomProduct(ByVal str As String) As Long Dim Matches As Object Dim Amount As Long Dim Days As Long With CreateObject("VBScript.RegExp") .Global = False .Pattern = "^\$(\d+) for days 1 through (\d+)" If .test(str) Then Set Matches = .Execute(str) Amount = Matches(0).submatches(0) Days = Matches(0).submatches(1) If Days >= 4 Then CustomProduct = Amount * 4 Else CustomProduct = Amount * Days End If End If End With End Function
Then you may use it on the Worksheet like this...
09-16-2019 11:26 PM
It doesn't seem to work, i guess we have to remove the $ symbol and also convert the text to number.
09-17-2019 01:17 AM
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?
09-17-2019 09:05 AM - edited 09-17-2019 09:45 AM
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') .
09-18-2019 03:28 AM
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.
09-18-2019 04:33 AM
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 this page, 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.