Forum Discussion
Solved
EXTRACT NUMBER WITH FORMULA
Hi everyone, can someone help me solve this issue?
If in a cell there is this term "DISCOUNT 15.00%" how can I extract only the number within text, so I can use it as source amount for calculation purposes?
I tried this formula but it seems to be telling me there is an error with %.
=RIGHT(B93,LEN(B93)-FIND(“%”,B93))
What I want to do is no matter what the input is, excel always extracts the number in between text so it can be used in calculation. "Main reason, if the above term is used, a discount amount based on the number will be applied. But how to extract the number?"
Hi,
Try
=VALUE(MID(B93,FIND(" ",B93)+1,FIND("%",B93)-(FIND(" ",B93)+1)))
It worked for me. I added "Value" just to get a number instead of a text.
Best regards
Per
Hi,
Try
=VALUE(MID(B93,FIND(" ",B93)+1,FIND("%",B93)-(FIND(" ",B93)+1)))
It worked for me. I added "Value" just to get a number instead of a text.
Best regards
Per
- Thank you for the response. It worked. Now just a question, how can I do it so it can search where ever the number is listed for example : I wont recommend colleagues to use account number or customers ID during input. But for a curiosity: How it can search for it where ever it is like "HAPPY COOKIES: CUSTOMER ID 11004420 DISCOUNT 15.00%" ?
- Hi,
In that case I would go for the reply Geir Hogstad suggested, but exchange the " " with "Discount ")
=VALUE(TEXTAFTER(TEXTBEFORE(B93,"%"),"DISCOUNT "))
(Observe the space after DISCOUNT)
Best regards
Per
=--RIGHT(B93,6)- =TEXTAFTER(TEXTBEFORE(D5;"%");" ") where the string "DISCOUNT 15.00%" is in D5.
- To prevent calculation errors, it's advisable to wrap this formula with VALUE()
- Right. By applying the formula, without finish it, it prompt me to check the formula.
