How to check if a cell contains "%" and calculate accordingly.

Solved/Closed

N245 - Mar 24, 2015 at 06:04 PM
Saurabh - Oct 18, 2019 at 02:46 PM

I am wondering how to do that?

I learnt no basics, just went on by myself trying to figure out things and so here is what I am trying to do..

Basically, if cell B2 contains a whole number (say 10), then cell C2 returns that flat value, but if cell B2 contains a percentage, then C2 performs a simple equation (i.e.: B2*B1)

It's just I am not sure how to define if a cell has a specific symbol/text so it can perform the equation needed, else disregard.

How I vision it could've been (tried but failed)"
IF(B2="#%", B2*B1, B2) -- if I understood that function correctly, if B2 contains a percentage, it would multiply B2 by B1, if not, it will return B2 value.. regardless of that, it always returns B2 value, even if it is with a percentage.

Help will be appreciated.

If cell contains specific text
Excel conditional formatting if cell contains specific text - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
Count if cell contains number - Excel Forum
If cell contains date then return value ✓ - Office Software Forum
If a cell has text then return value ✓ - Excel Forum

2 responses

Answer 1 / 2

MaxStart Posts 339 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Mar 25, 2015 at 07:23 PM

=IF(RIGHT(B2,1)="%",B2*B1,B2)

please follow-up with us if it works for you.
--
Damn it !!!, is it ever gonna be the way I want it to be?

N245
Mar 26, 2015 at 04:34 AM

Works like a charm!

Could you possibly explain what does the function "right" mean?

MaxStart Posts 339 Registration date Tuesday March 3, 2015 Status Moderator Last seen July 3, 2015 69
Mar 26, 2015 at 04:46 AM

it will return characters from the string starting from the right, number of characters in this case is 1

Saurabh
Oct 18, 2019 at 02:46 PM

Thanks Max

Answer 2 / 2

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 25, 2015 at 05:52 AM

try this formula

=IF(ISERROR(SEARCH(".",A1)),B2*B1,B2)

N245
Mar 25, 2015 at 06:41 AM

I am not quite sure why you included A1 or even why there is a dot, but anyways the formula didn't work unfortunately.

It does the opposite of what happened with my code, always returned the "true" value, which is B2*B1 regardless of the presence of % or not.

N245
Mar 25, 2015 at 07:00 AM

I think I got this, since I can't get my way to return a true/false value if B2 cell contains the symbol % I just used this formula:

=IF(B2<=1, B2*B1, B2)

Where 100% would be 1.00, 99% would be 0.99 and so on are less than 1.. now it works like a charm. However, if the percentage input is more than 100% it wouldn't work, but I doubt I'd need more than that. Anyhow, I still would like to know if a cell contained a symbol or a text, which expression do I use to return a true/false value for that cell and upon it perform another function?

Similar discussions

Percentage calculation

Windows can't format

Excel - IF function with multiple words

If cell = date return yes or no.

Security check preventing login problem

Security checks preventing log in

Similar discussions

Subscribe To Our Newsletter!