Null Values Help

[Aliq2014]

Can anyone help with this I suppose easy formula?
I am trying to return a value. For example, </SPAN>=if(left([Text1],1)="N",[Text1],"A"&[Text1]) (this is a powerpivot formula)</SPAN></SPAN>
I want to return Text1 with an N123 or A234. I've noticed that I have blank values in Text1 and is causing to populate “A” instead I want to populate “0” or “Blank” if text1 is blank?

Anyone have any suggestions?

 

[Joe4]

Try imbedding your formula in a Nested IF statement which first checks to see if there is anything in Text1, i.e.
=if([Text1]="",0,if(left([Text1],1)="N",[Text1],"A"&[Text1]))

 

[Aliq2014]

Try imbedding your formula in a Nested IF statement which first checks to see if there is anything in Text1, i.e.
=if([Text1]="",0,if(left([Text1],1)="N",[Text1],"A"&[Text1]))

Hello Joe4, thanks for your comments. I'm trying to code your suggestions. However, Im getting parsing errors. I evaluated =If([Text1]="",0) this works. I'm having trouble with the second part of the syntax to eavaluate non-blanks values.
IF(logical_test>,<value_if_true>, value_if_false)

I appreciate your assistance and cooperation.

 

[Aliq2014]

that is ** IF(logical_test, value_if_true<VALUE_IF_TRUE>, value_if_false)

 

[Joe4]

Doesn't my formula work for you, as-is?
Note that your formula has incorrect syntax. You are missing a value after your Greater Than sign.

 

[Aliq2014]

I copy and paste your formula and all the values returned with a #Error value. The error stated the "second and third argument of function IF have different data types. This is not suppoerted".

 

[Joe4]

Hmmm...

I missed originally that it was a PowerPivot formula (we have a separate forum for that). I will move this question to that forum.
Maybe PowerPivot formulas have different structures than regular Excel formulas do. Hopefully someone who knows more about Power Pivot can answer.

 

[Aliq2014]

Hmmm...

I missed originally that it was a PowerPivot formula (we have a separate forum for that). I will move this question to that forum.
Maybe PowerPivot formulas have different structures than regular Excel formulas do. Hopefully someone who knows more about Power Pivot can answer.

Thank you.

 

[Jerry Sullivan]

Can anyone help with this I suppose easy formula?
I am trying to return a value. For example, </SPAN>=if(left([Text1],1)="N",[Text1],"A"&[Text1]) (this is a powerpivot formula)</SPAN></SPAN>
I want to return Text1 with an N123 or A234. I've noticed that I have blank values in Text1 and is causing to populate “A” instead I want to populate “0” or “Blank” if text1 is blank?

Anyone have any suggestions?

If the Data Type for your Text1 field is "Text" then the only change needed to the formula Joe suggested is to put quotes around the zero:
=IF([Text1]="","0",IF(LEFT([Text1],1)="N",[Text1],"A"&[Text1]))

You can check the existing Data Type of the Text1 field from the Data Model Ribbon > Home tab > Formatting group > Data Type:

If the Data Type is anything other than Text, change it to Text.

 

[theBardd]

Try

=IF(ISBLANK([@Text1]),"",IF(LEFT([@Text1],1)="N",[@Text1],"A"&[@Text1]))