Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- extracting number from text string and doing calculations

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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

Labels:

7 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-16-2019 08:59 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

In B2

`=CustomProduct(A2)`

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Thanks again

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Microsoft Store

Education

Developer