excel If Statement is false but value is true

Dear experts,

in the formula below i'm getting "false" but in worksheet IMPORT!Q2 the value is web-only-user
Whats the problem?

=IF(IMPORT!Q2="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null"

Open in new window

LVL 2
Mandy_Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
I'll add to that (and say that you need a close-bracket at the end :-) ).

=IF(lower(TRIM(IMPORT!Q2))="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null","")

Open in new window


If that doesn't work, try going to Formulas - Evaluate Formula so you can step into the formula while it executes.
0
 
James ElliottConnect With a Mentor Managing DirectorCommented:
Try:

=IF(TRIM(IMPORT!Q2)="web-only-user";"Set-CASMailbox -Identity "&A2&" -PopEnabled $false -ImapEnabled $false -ActiveSyncEnabled $false -MAPIBlockOutlookRpcHttp $true -MAPIEnabled $true -OwaMailboxPolicy $null"
0
 
Mandy_Author Commented:
Evaluate Formula shows me that the part  LOWER(TRIM(IMPORT!Q2))="web-only-user"  = false

but the value in  IMPORT!Q2    excactly is web-only-user


 $null","") not working


 $null""")  shows false

and
$null";"")   shows empty cell

???
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What does this give?

="+" & import!q2 & "+"

Open in new window


and

=len(import!q2)

Open in new window

0
 
James ElliottManaging DirectorCommented:
There are actually two different ascii characters for a symbol that looks like '-'. I can't remember the actual codes off hand, but try re-typing Q2 and then type the formula with exactly the same key choice.
0
 
Mandy_Author Commented:
"+web-only-user+"

and
=len(import!q2)

empty cell
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Those two answers are so strange - can you please post your spreadsheet.
0
 
Mandy_Author Commented:
if i change web-only-user to "web" in import!q2 everything working fine. Thats an issue with "-"
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Got it! (I think)

Change

=IF(IMPORT!Q2="web-only-user";

to =IF(IMPORT!Q2="web-only-user",

It should be a comma, not a semi-colon.

Trouble is, your initial formula won't actually work, so if this doesn't work, we are a little in the dark helping you. So please then post your spreadsheet.
0
 
Mandy_Author Commented:
if i change q2 and type web-only-user it's also working. The data in this column are coming from powershell.

and if we search in the formula for "web" in q2?
0
 
James ElliottManaging DirectorCommented:
I doubt the semi-colon is the issue. You can change the standard delimiter and some regions have the semi colon as standard.

I refer to my previous post. The issue looks like the ascii character used to do the hyphen (or the minus). There are at least two different possibility within the standard ascii set.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please post your spreadsheet. There's nothing more I can help you with unless you do so.
0
 
James ElliottManaging DirectorCommented:
When typing your formula, instead of hitting the hyphen, instead hit ALT+196 and see if that works.
0
 
Mandy_Author Commented:
My fault!  the CSV was written in ACSII Encoding. I changed to default and now all working fine.

Thank you so much for your help!
0
All Courses

From novice to tech pro — start learning today.