-1

I'm going to make a career out of this problem. I had the previous issue and that so gallantly solved by JvdV in:

Excel: Custom Data Validation for numbers 0-9 or comma separated values 0-9

Now, they want me to allow the letter e or E to the mix. To just allow the e I tried:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(B3,",","</s><s>")&"</s></t>","//s[preceding::*=.  or .*0!=0 or .*1>9 or .*1<0 or .*0!='e']"),"")=""

but that didn't work so I'm asking for help again...

SJR
  • 22,233
  • 6
  • 16
  • 23
Velocedge
  • 708
  • 5
  • 18
  • 1
    JvdV posted a useful looking link in that answer. Have you read it? – SJR Nov 16 '20 at 22:46
  • 1
    Does this answer your question? [Excel - Extract substring(s) from string using FILTERXML](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml) – EEM Nov 17 '20 at 00:17

2 Answers2

2

Haha, you know the drill by now. First, I followed the outlined solution but then decided to give it another spin this time:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*=. or not(translate(., 'abcdfghijklmnopqrstuvwxyzABCDFGHIJKLMNOPQRSTUVWXYZ', '')) or string-length(.)!=1]"),"")=""

The general structure stays the same: The IFERROR-function is applying FILTERXML to convert the cell(s) into queryable XML nodes, then using XPath 1.0 functions to extract/transform content that is not/allowed, and finally, compare it to "" to get a boolean result (phew).

  • No duplicate values: selection non-unique values preceding::*=.
  • filter every alphanumeric value expect 0-9 e E: not(translate(., 'alphaNum!09eE', ''))
    If you need to disallow other characters add them to the list.
  • only allow individual elements of length 1: string-length(.)!=1
  • allowing , as a separator
  • if you only want to allow e regardless of case LOWER() the cell content first

Here is a variation that is closer to the idea of the original formula; it does not work with a list of filtered characters, however, it's even longer:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(LOWER(A1),",","</s><s>")&"</s></t>","//s[preceding::*=.  or not(contains(.,'e')  or number(.)<10 or number(.)>-1) or string-length(.)!=1]"),"")=""
wp78de
  • 16,078
  • 6
  • 34
  • 56
  • Be aware that this allows for a string like `#,$,!` to be entered. I applaud you for having a go at it =) – JvdV Nov 18 '20 at 10:30
  • I've never had cause to use xpath before. Your answers and explanations have been very helpful. – Velocedge Nov 18 '20 at 11:51
  • I didn't want to make this look like a copy & paste job just adding the `e`, so I was playing around and came up with the idea of the list, and noted myself that additional disallowed characters need to be added to the list; the second variation does not have this limitation, primarily, I tried to make the solution more readable. – wp78de Nov 18 '20 at 15:03
  • @wp78de, I understand and it certainly can work, therefor I upvoted. As of right now, another issue (depending if OP mentioned *"allow the letter e **or** E to the mix"* on purpose) is that you allow for a string like `1,2,e,4,E`. Either way kudo's to you and let's just say that `FILTERXML()` is so much fun on Excel. – JvdV Nov 18 '20 at 15:27
  • Except XPATH is actually case-sensitive! This won't pick up on `E` nomore now. Add `LOWER()` in a nested role within the `SUBSTITUTE()` and you'll be there =). – JvdV Nov 18 '20 at 16:12
  • @JvdV ha, you are right, I was not paying attention when I made the change, just checked the test case and missed the LOWER(). – wp78de Nov 18 '20 at 16:36
2

Try:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(LOWER(A1),",","</s><s>")&"</s></t>","//s[string-length(.)!=1 or (.*0!=0 and .!='e') or preceding::*=.]"),"")=""

An explaination of the XPATH:

  • //s - Select s-nodes that:
    • string-length(.)!=1 - Node not made of a single character.
      • or - Or check against:
    • (.*0!=0 and .!='e') - Node not equal to zero if multiplied by zero AND node not equal to "e").
      • or - Or check against:
    • preceding::*=. - Node has a duplicate.

In your previous question I linked you to this Q&A. I suggest you study this, and try to get familiar with XPATH syntax if this is a reoccuring problem you have to solve within your work.

JvdV
  • 41,931
  • 5
  • 24
  • 46
  • @Velocedge, a small note to make here: `LOWER()` prevents the use of both "e" and "E" at the same time. So if you want that to be possible you'd need to remove that nested function and extend the or logic in the part of the XPATH logic that is in between paranthesis. – JvdV Nov 18 '20 at 13:03