Excel 2013 - Multiple numbers in cell as text string, need to identify if any are less than certain value

Question

I have been given a large amount of data (and can't go back and ask them to provide it in a more useful manner). It contains multiple figures in the same cell, separated by a semicolon.

  A
 +-------------------------+
1|Sats                     |
 +-------------------------+
2|92; 89; 94; 99; 100      |
 +-------------------------+
3|95; 98; 96               |
 +-------------------------+
4|99                       | 
 +-------------------------+
5|88; 95                   |
 +-------------------------+
6|99; 95; 98; 98; 100; 95  |
 +-------------------------+
7|94; 92; 91; 93           |
 +-------------------------+

Somehow, I need to identify whether or not a cell includes a number lower than 94. Just a yes/no, fortunately I don't need any greater analysis than that.

Or alternatively, to list the lowest number in each cell. Anything that can show if there's a number below 94.

Unfortunately, the number of different readings in each cell is unlimited - otherwise, I'd have nested formulae checking the first, second, third etc entry.

Does anyone have any ideas? This has completely stumped me.

Should've said that I want to avoid text to columns if it's at all possible - I didn't make myself clear, the fact that the number of readings in each cell is unlimited means I can't just create some columns, I don't know how many to make. Could be over a hundred. — RolloTreadway, Apr 26 '19 at 10:09
(1) The number of columns is irrelevant (2) If not TTC you'll need VBA. — SJR, Apr 26 '19 at 10:13
Also, I was trying to figure out something on this basis: IF(LEFT([@Stat],((SEARCH(";",[@Stat])))-1),"Y" then using LEN(LEFT([@Stat],((SEARCH(";",[@Stat])))-1)) with MID to select the next number, and then repeating that for the next and so on... but I don't know if that can work with an unlimited string of numbers. — RolloTreadway, Apr 26 '19 at 10:18
I'll try to figure out a VBA approach. Might end up asking another question about that, but I'll do my best to figure it out myself. Thanks. — RolloTreadway, Apr 26 '19 at 10:19
I think you'll probably want to use the split function to pass the contents to an array and then check if any instance of the array is greater than 94. You can find guidance on split here https://www.exceltrick.com/formulas_macros/vba-split-function/ — will1329, Apr 26 '19 at 10:22
I think I might delete this question. Not sure it's going to be of any benefit to other users. Thanks everyone. — RolloTreadway, Apr 26 '19 at 10:25
@RolloTreadway See my [answer](https://stackoverflow.com/a/55865614/388606) for a formula as an alternative to VBA. — i_saw_drones, Apr 26 '19 at 10:34

score 1 · Answer 1 · answered Apr 26 '19 at 10:23

You can create a UDF for this.

create a new module in VBA (Alt +F11)

Then use the below code:

Public Function ParseAndSum(source As String) As Integer
       Dim tmp() As String, i As Integer
       tmp = Split(source, ";")
       For i = LBound(tmp) To UBound(tmp)
       ParseAndSum = Val(tmp(i)) < 94
       Next i
End Function

In B2 use the formula :

=ParseAndSum(H19)<0

If there is a value below 94 in there string within A2, it will return TRUE, else it will return FALSE

i_saw_drones · Accepted Answer · 2019-04-26T10:35:32.800

1

Based on this answer, you can use the below array formula (don't forget to Ctrl+Shift+Enter!) in cell B2 to return a TRUE or FALSE if your list of numbers contains a number less than 94:

 =SUM(--(1*TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A2)-LEN(SUBSTITUTE(A2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A2)-LEN(SUBSTITUTE(A2,";",""))+1)))=1),99))<94))>0

You can then just drag this down in column B and it should work, however, you may need to also replace the spaces between your delimiters ; so it is one continuous list in each cell, e.g. 92;89;...

edited Apr 26 '19 at 10:35

answered Apr 26 '19 at 10:25

i_saw_drones

3,486
1
31
50

This is exactly what I was aiming for! (Although I'm also going to try and learn the VBA split thing, because I think that'll be very helpful in future). It works perfectly. Thank you so much. (Also, rather than replacing the space, I switched ";" to "; ".) – RolloTreadway Apr 26 '19 at 10:37

score 0 · Answer 3 · answered Apr 26 '19 at 09:38

0

One way would be to use PowerQuery (assuming you are one 2013 or later). To do this select you data, go to Data>From Table/Range. The Power Query window will open. Go to Transform>Split Column>By Delimiter. You can trim the result to remove spaces by clicking on Format>Trim.

answered Apr 26 '19 at 09:38

will1329

173
2
14

I am using 2013, but there's no PowerQuery, sorry. – RolloTreadway Apr 26 '19 at 10:10
@RolloTreadway It is available as a free download from Microsoft. – Ron Rosenfeld Apr 26 '19 at 10:21
https://www.microsoft.com/en-us/download/details.aspx?id=39379 (but requies admin rights that you may not have if on a work computer) – will1329 Apr 26 '19 at 10:23
I don't have admin rights. I'll have a word with IT, see what their position is. – RolloTreadway Apr 26 '19 at 10:29

score 0 · Answer 4 · answered Apr 26 '19 at 09:40

0

Put this column into C, then choose this column, use Data->Text to columns, and split by semicolon, this will split to D, E, F...

After that, at B column write =MIN(C1:Z1), or you can put more large cell number.

Then at A column write =IF(C1>94, "YES","NO"). then filter by A column.

answered Apr 26 '19 at 09:40

BeiBei ZHU

343
2
12

I was hoping not to split into multiple columns, because I've no way of knowing how many columns will be required, and in same cases it'll be a few dozen. – RolloTreadway Apr 26 '19 at 10:02

score 0 · Answer 5 · answered Apr 26 '19 at 10:34

I'm pretty certain you have the FILTERXML function in 2013.

You can use that to split the string, and then test it in the array formula:

=OR(FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s")<94)

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

Excel 2013 - Multiple numbers in cell as text string, need to identify if any are less than certain value

5 Answers5