IF Statement

[vicedo]

Hi guys, please i know this is very easy but i'm not just thinking straight.

I'm trying to compute an IF statement for this

Edozie, Victor

=LEFT(Staff_Independence[@Name],FIND(",",Staff_Independence[@Name])-1)

Edozie

<tbody>
</tbody>

I don't want the name in the last column, instead, i want it blank

 

[Scott Huish]

What is your IF statement supposed to do exactly? What are you checking for?

 

[vicedo]

The formula in second column evaluates to true and thats why i'm getting Edozie. However, i don't want Edozie, instead, i want it blank

 

[Scott Huish]

The formula you posted can't evaluate to TRUE because that formula will return a string. I don't quite understand. Assuming your formula points to the cell to the left of that formula it is doing exactly what you told it to do. What is the formula in the cell that contains Edozie and how do you want to change it?

Basically, this is all you have supplied thus far.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:136px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Edozie, Victor</td><td >Edozie</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=LEFT(A1,FIND<span style=' color:008000; '>(",",A1)</span>-1)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

Can you please expand on what you want?

 

[vicedo]

Yea the formula is doing the right thing but is there no way to compute an if statement to return blank instead of Edozie.

There is no formula in the cell that contains edozie, its just the result of the formula in question

 

[Asala42]

The formula in second column evaluates to true and thats why i'm getting Edozie. However, i don't want Edozie, instead, i want it blank

Delete the formula entirely. That would make it blank. Or replace the formula with the simpler:

=""

Scott's trying to understand the logic you are trying to apply. You imply that you need to incorporate an if statement into it but you have not told us what that logic is. Is there some criteria you are using that tells you that particular entry should be blank but perhaps not others? If so, show examples of entries you want to result with blanks, examples of entries where you use the Left(Find()) formula above, and why. In any case I imagine that formula structure would look like this:

 =IF(Staff_Independence[@Name]="Edozie, Victor","",LEFT(Staff_Independence[@Name],FIND(",",Staff_Independence[@Name])-1))

or simplified

=IF(criteria,"",originalformula)

 

[vicedo]

Now i understand you better but i have a column which contains real names and some dummy names. real names such as Edozie, Victor and dummy names such as Accountsmbx and sometimes, the dummy names have spaces in them as well. The real names are seperated by comma's whilst the dummy names doesn't. So i need an IF formula which returns only the dummy names and returns blanks where there are real names.

 

[Asala42]

Ah so if I read you right, if the name contains a comma you want the formula to result in a blank, and if not you want to pull the name (when it's a dummy name)

Try:

=IF(LEN(Independence[@Name])=LEN(SUBSTITUTE(Independence[@Name],",",""),Independence[@Name],"")

This should also work if you want to use FIND

=IF(ISERROR(FIND(",",Independence[@Name]),Independence[@Name],"")

edit - added "FIND" formula also

 

[vicedo]

Thanks alot...It worked perfectly

 

[Scott Huish]

If you are using XL2007 or higher:

=IFERROR(T(FIND(",",A1)),A1)

Change A1 to your cell reference.