SOLVED

# EXTRACT NUMBER WITH FORMULA

Copper Contributor

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

8 Replies

# Re: EXTRACT NUMBER WITH FORMULA

=TEXTAFTER(TEXTBEFORE(D5;"%");" ") where the string "DISCOUNT 15.00%" is in D5.

# Re: EXTRACT NUMBER WITH FORMULA

``=--RIGHT(B93,6)``
best response confirmed by SoyAllenChiu (Copper Contributor)
Solution

# Re: EXTRACT NUMBER WITH FORMULA

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

# Re: EXTRACT NUMBER WITH FORMULA

To prevent calculation errors, it's advisable to wrap this formula with VALUE()

# Re: EXTRACT NUMBER WITH FORMULA

Right. By applying the formula, without finish it, it prompt me to check the formula.

# Re: EXTRACT NUMBER WITH FORMULA

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%" ?

# Re: EXTRACT NUMBER WITH FORMULA

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

# Re: EXTRACT NUMBER WITH FORMULA

Hello there, thank you all for the help. I found another issue, but i don't know how to explain. It doesn't relate to this article so I will put in a new Article when I know exactly what I want to do and what areas to use the formula in. The formula itself provided by you all, works fine after those advice. Now I have some other issues That kind a make it hard to solve. Thank you.. Also sorry for reporting late, I have been busy lately, But thank you all <3
1 best response

Accepted Solutions
best response confirmed by SoyAllenChiu (Copper Contributor)
Solution

# Re: EXTRACT NUMBER WITH FORMULA

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