Hi all I have many text strings that contain a series of numbers all separated by a space.
These are in column A, in Column B I would like to know how many numbers are in each string that are lower than 75
So in the example below the return would be 2
18 22 90
Example below would be 4
2 33 49 65
Any help appreciated
I suppose that you could use an array formula for that, but that is beyond me.
I would have to use a user defined function.
Enter your string in A1 and this formula into B2
Function NumCount(A As String, B As Long) MyArray = Split(A, " ") For Count = 0 To UBound(MyArray) - 1 If MyArray(Count) < B Then NumCount = NumCount + 1 Next End Function
Formula:
=NumCount(A1,75)
Last edited by mehmetcik; 05-21-2017 at 06:22 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Here is an array formula that will do it
Formula:=COUNT(IF(IFERROR(--MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*(COLUMN(A:Z)-1)+1,99),"")<75,--MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*(COLUMN(A:Z)-1)+1,99)))
**Must be entered with Ctrl+Shift+Enter key combination.
v A B 1 18 22 90 2 2 2 33 49 65 4 3 99 45 65 79 110 63 3 4 1225 33 489 63 15 78 1300 33 8 11 3 7
Last edited by AlKey; 05-21-2017 at 06:38 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Great stuff thanks both.
Mehmetcik there were some instances were this did not calculate correctly, so I have used AlKey's method which I was a bit wary of as Array formula's can be quite heavy on a large data set but it seems to work perfectly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
cmb80 
Register To Reply
Bookmarks