Finding the values greater than and less than X from this comma seprated data in excel-CodePudding

Finding the values greater than and less than 75 from this comma separated data 100,150,75,50,10,30,250

CodePudding user response：

The best way to do this is with VBA. That solution is trivial, so I'll give it first, and I don't think it's going to need explanation. If you are constrained to use a formula, I'll give a solution for current Excel using LET and FILTER. I'll explain that one first, since it's easier to think about than the solution without them.

VBA:

Function getValues(str As String) As String
    
    Dim a As Variant, arr As Variant, buffer As String, comma As String
    arr = Split(str, ",")
    buffer = ""
    comma = ""
    
    For Each a In arr
        If a < 75 Then
            buffer = buffer & comma & a
            comma = ","
            End If
        Next
    
    getValues = buffer
    
End Function

This will return all of the values less than 75 in a comma separated list. I'm only giving the example for <75, because I'm not exactly sure what your conditions are, and I think it will be simple enough for you to adapt the comparison to anything.

In Excel 365 (with dynamic array functions), assuming your list is in A1:

=LET(
  vals, LET(
        
    n,         100,
    length,    LEN(A1)-LEN(SUBSTITUTE(A1,",","")) 1,
    str,       SUBSTITUTE(A1, ",", REPT(" ", n)),
    container, ROW(OFFSET(A1, 0, 0, length)),
    
    1*MID(str, IF(container=1, 1, (container-1)*100), n)
    
  ),
  FILTER(vals, vals<75)
)

First, LET allows us to define sets of pairs, a name and a value (which can be a formula), and then use those pairs in the formula, which is at the end. I'll walk through the interior LET function.

n: this is an arbitrarily large number. We'll get to how this is used in a little bit.

length: this is the number of values in the string, calculated by replacing the commas in the original string and subtracting that length from the length of the original string.

str: this is the original comma separated string with the commas replaced by n spaces.

container: OFFSET is used here to construct an array of the same size as the number of values in the list. The arguments are reference, row offset amount, column offset amount, [height]. The row and column offsets are 0. We don't really want to offset anything, we're just trying to return an array of the right size for later. The optional argument height is the trick. We pass that length from the LET definitions. The reference I am using is A1, but it doesn't matter, since we're not concerned with the values. Any cell reference will work here.

Once the array is built, we wrap the OFFSET function in ROW, and that will produce a number sequence from 1 to length.

Now, the formula:

1*MID(str, IF(container=1, 1, (container-1)*100), n)

What we are going to do here is extract the values from the string that has the commas replace for n spaces. Since we replaced the commas with a sequence of n spaces, we know that each number comes no earlier than 100 times something and is surrounded by spaces, so all we need to pass to MID is the starting points for each extraction: 1, 100, 200, 300, etc., and we will extract n characters at a time.

To get those indexes, we are using the container array we generated earlier. We need to pass the row numbers * 100, but we just have to pass 1 for the very first one, so that we don't give MID a 0 and generate an error.

This will generate the list of all values, but remember that they are padded strings, so we multiply the whole thing by 1 to get them back to numbers. I think you could also use --.

Now take that interior LET and wrap it in another LET so we can assign the output of that formula to another variable (vals) and then we can just use FILTER to return only the values less than 75.

And that is that. If you need these back as a comma separated list, wrape the output of this in TEXTJOIN. One note, eventually the extraction will break down, because the values aren't zero width. In that case you can adjust the size n up OR you could take the average length of the values and compensate for them in the padding. I'm not going to demonstrate that here, but just be aware that you might have to.

There is a way to do this for Excel 2007 up, but I will add that a little bit later. It is ugly as can be. And here's hoping I didn't make any typos copying this all out of Excel. Use the VBA solution if at all possible.