 • 461K Members
• 5,515 Online
• 559K Conversations

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

7 Replies

# Re: extracting number from text string and doing calculations

@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

# Re: extracting number from text string and doing calculations

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)`` # Re: extracting number from text string and doing calculations

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

# Re: extracting number from text string and doing calculations

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?

# Re: extracting number from text string and doing calculations

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

# Re: extracting number from text string and doing calculations

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

# Re: extracting number from text string and doing calculations

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. Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies