Forum Discussion

Copper Contributor

Sep 17, 2019

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

Book1.xlsx10 KB

excel

Formulas and Functions

Haytham Amairah

Silver Contributor

Sep 17, 2019

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

Solution.xlsx9 KB

vicasso
Copper Contributor
Sep 17, 2019
Haytham Amairah

It doesn't seem to work, i guess we have to remove the $ symbol and also convert the text to number.
- Subodh_Tiwari_sktneer
  Silver Contributor
  Sep 17, 2019
  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?
  - vicasso
    Copper Contributor
    Sep 18, 2019
    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 🙂