CODEWITHSUNDEEP
excelexcel-formula
Legion
Legion

Reputation: 3437

Why is this switch formula returning #N/A?

The string it's searching (A2) is:

<Column Name="Col1" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />

=SWITCH(A2, ISNUMBER(FIND("Int32", A2)), "Int32", ISNUMBER(FIND("DateTime", A2)), "DateTime",  ISNUMBER(FIND("String", A2)), "String",  ISNUMBER(FIND("Double", A2)), "Double",  ISNUMBER(FIND("Decimal", A2)), "Decimal",  ISNUMBER(FIND("Boolean", A2)), "Boolean")

It should return Int32 but instead it returns #N/A.

Why?

Upvotes: 0

Views: 73

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Your data is a xml format, you could use FILTERXML function as in,

In B2, enter formula :

=FILTERXML(SUBSTITUTE(A2,"System.",""),"//@Type")

enter image description here

Upvotes: 2

BigBen
BigBen

Reputation: 50008

Use TRUE as the first argument:

=SWITCH(TRUE, ISNUMBER(FIND("Int32", A2)), "Int32", ISNUMBER(FIND("DateTime", A2)), "DateTime",  ISNUMBER(FIND("String", A2)), "String",  ISNUMBER(FIND("Double", A2)), "Double",  ISNUMBER(FIND("Decimal", A2)), "Decimal",  ISNUMBER(FIND("Boolean", A2)), "Boolean")

The problem is that in its current form, you're attempting to compare the entire text of A2 to the result of ISNUMBER(FIND()), which is a boolean.

Another option is IFS:

=IFS(ISNUMBER(FIND("Int32", A2)), "Int32", ISNUMBER(FIND("DateTime", A2)), "DateTime",  ISNUMBER(FIND("String", A2)), "String",  ISNUMBER(FIND("Double", A2)), "Double",  ISNUMBER(FIND("Decimal", A2)), "Decimal",  ISNUMBER(FIND("Boolean", A2)), "Boolean")

but.... a much nicer solution from @ScottCraner using LET and FILTER:

=@LET(x,{"Int32","DateTime","String","Double","Decimal","Boolean"},FILTER(x,ISNUMBER(FIND(x,A2))))

Upvotes: 2

Related Questions