CODEWITHSUNDEEP
excel-formula
David Erickson
David Erickson

Reputation: 16683

Syntax for an excel formula for IF a cell contains ANY characters that are NOT numbers or hyphens?

I have researched a lot, and couldn't figure this out. I have written an extremely long Nested If statement. The last piece to the puzzle follows (The bolded part obviously isn't close to a formula, but I was struggling to build the logic behind that):

IF((**F85 contains any characters that are NOT "1" "2" "3" "4" "5" "6" "7" "8" "9" "0" "-"**),1,0)

I am looking for an IF statement that returns a 1 or 0 if any character in cell F85 is not a number or dash. For example:

This is NOT relevant so ignore, but to show I've made some progress, this is my NestedIF so far, but I just need the last piece to the puzzle (no need to combine above formula with my NestedIf - I can take it from there):

=IF(OR(F85="None",F85=""),"00000-00000",IF(ISTEXT(SEARCH("-",F85)),F85,(IF(AND(LEN(F85)<=10,ISTEXT(SEARCH("-",F85)))="FALSE",LEFT(TEXT(F85,"0000000000"),5)&"-"&RIGHT(TEXT(F85,"0000000000"),5),F85))))

Thank you, this is the final part of a long macro that I've written, so it is very much appreciated if you can help.

Upvotes: 0

Views: 533

Answers (2)

pilchard
pilchard

Reputation: 12920

Since this seems to have been resurrected here is an approach based on removing all the dashes, and multiplying by 1.

=IF(AND(ISERROR(SUBSTITUTE(F85,"-","")*1),LEN(SUBSTITUTE(F85,"-",""))<>0),0, 1)

If multiplying the result of SUBSTITUTING all dashes returns an error, and the length of that result wasn't empty, then return 0. Else the result was all numbers so return 1.

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152525

Use this:

=--(SUMPRODUCT(--(ISERROR(FIND(MID(F85,ROW($A$1:INDEX(A:A,LEN(F85))),1),"1234567890-"))))=0)

enter image description here

Upvotes: 2

Related Questions