extracting number from text string and doing calculations

[vicasso]

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 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)

Example
1) The first sum should be 280*4
2) second sum should be 240*3

[#2]

@vicasso

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

[#3]

@vicasso 

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...

In B2

=CustomProduct(A2)

[#4]

@Haytham Amairah 

It doesn't seem to work, i guess we have to remove the $ symbol and also convert the text to number. 

[#5]

@vicasso 

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?

[#6]

@vicasso 

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') .

[#7]

Hi @Subodh_Tiwari_sktneer ,

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 :)

[#8]

@vicasso 

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.