0

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:

  • 12345-12345 would return a 1
  • 1234512345 would return a 1
  • ---------- would return a 1
  • 12345/12345 would return a 0
  • 12345a12345 would retun a 0
  • abc123 would return a 0
  • abc-123 would return a 0

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.

David Erickson
  • 16,433
  • 2
  • 19
  • 35
  • this might help... https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Alan Aug 17 '18 at 03:44
  • Hi Alan, thanks for sharing. A lot of advanced ideas in there, so Scott's formula does the trick. I did go through some of that post though, so I appreciate it. – David Erickson Aug 17 '18 at 18:41

2 Answers2

2

Use this:

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

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Hi Scott, I have plugged this into my formula and it works perfectly, thank you so much. Later, I am going to have to breakdown your formula and try to get my mind around it for my own learning. – David Erickson Aug 17 '18 at 18:42
  • It is literally going character by character in the cell and seeing if the character exists in the string of allowed characters. If any are not found it errors and the sumproduct is counting the errors. If no errors then everything is good. – Scott Craner Aug 17 '18 at 18:44
1

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

pilchard
  • 12,414
  • 5
  • 11
  • 23