How do you use an If Statement for a number in a Text formatted cell

Modify_inc

Board Regular
Joined
Feb 26, 2009
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
Ok, I love using the IF Statement, it's very powerful and versatile, but I have a question as I'm still learning how to use it in Excel.

I have a column of data (E4:E29) that goes like this:
8=D
3=A
1=W
0=V
0=Q
etc.. for all letters in the alphabet, so 26 times, and it's always randomly generated, so 8=D could change to 0=D the next time around.

All I want to do is hide or NOT display any data that starts with a 0. So 0=V and 0=Q in this instance, needs not be displayed.

Since theses cells are formatted using Text, how can I use an If Statement to determine if the first letter = 0?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
How about
Excel Formula:
=if(left(e4)="0","y","n")
 
Upvote 0
How about
Excel Formula:
=if(left(e4)="0","y","n")
That was quick!

I was just about to post, I figured out a way almost by using Conditional Formatting using similar code and just making the cell all white:
=LEFT(E4,1)="0"
But because I use the =MOD(ROW(),2)=0 to make each other row white and grey, they still show on the grey rows

I will try your suggestion, though do you know an easy way around the issue above?
 
Upvote 0
You would need two rules, you can use your existing rule for the white cells & then use
Excel Formula:
=and(left(e4)="0",mod(row(),2)=0)
for the grey cells
 
Upvote 0
How about
Excel Formula:
=if(left(e4)="0","y","n")

That was quick!

I was just about to post, I figured out a way almost by using Conditional Formatting using similar code and just making the cell all white:
=LEFT(E4,1)="0"
But because I use the =MOD(ROW(),2)=0 to make each other row white and grey, they still show on the grey rows

I will try your suggestion, though do you know an easy way around the issue above?
Actually forget the Conditional Formatting, your suggestion works must better!

Thanks for the quick and helpful suggestion!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
How about
Excel Formula:
=if(left(e4)="0","y","n")
This formula above worked great until something unexpected happened that I didn't account for.

As long as the number is a single digit, which is majority of the time, it works great! But occasionally it will be a two digit number like 10, 11, 12, etc., and this formula will only register the first digit, which would be a 1 in this instance.

How can I revise it so it will recognize if anything before the "=" is a single number or a two digit number.
Remember, my data looks like the following for each cell:
8=D
3=A
1=W
10=S
11=N

etc...

This is the formula I use it in, so it must work in this formula:
VBA Code:
=IF(BJ4="","",IF(BJ4="","",COUNTIF($A$4:$A$50,RIGHT(BJ4)&"*")&"  of  "& LEFT(BJ4) & "" & RIGHT(BJ4)&"'s  Found!"))
 
Upvote 0
How about
Excel Formula:
=IF(BJ4="","",COUNTIF($A$4:$A$50,RIGHT(BJ4)&"*")&"  of  "& LEFT(BJ4,FIND("=",BJ4)-1) & "" & RIGHT(BJ4)&"'s  Found!")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top