Sep 05 2021 02:18 PM
Please help me, I want to use formula to extract numbers that has 4 or 5 at the beginning of a value. Thanks in advance.
Example:
15677
16577
37588
46777
24689
57438
68495
SOLUTION:
46777
57438
Sep 05 2021 02:30 PM
Let's say your numbers are in A2:A8.
1) A formula that works in all versions of Excel.
Enter the following formula in C2, and confirm it with Ctrl+Shift+Enter (this is essential):
=IFERROR(INDEX($A$2:$A$8, MATCH(0, IF((LEFT($A$2:$A$8)="4")+(LEFT($A$2:$A$8)="5"), COUNTIF($C$1:$C1, $A$2:$A$8), ""), 0)), "")
Fill down as far as you want.
2) A formula that works in Excel 365 only.
Enter the following formula in C2:
=SORT(UNIQUE(IF((LEFT($A$2:$A$8)="4")+(LEFT($A$2:$A$8)="5"),$A$2:$A$8,"")))
Sep 05 2021 02:59 PM
Sep 05 2021 03:12 PM
Did you confirm it with Ctrl+Shift+Enter?
Sep 05 2021 03:21 PM
SolutionSince you are sorting and filtering numbers you could use
= SORT(FILTER(numbers, (numbers<60000)*(numbers>=40000)))
in Excel 365 or
= IFERROR(
SMALL(
IF(
(numbers<60000)*(numbers>=40000),
numbers
),
1+ROW()-MIN(ROW(numbers))
),
"")
in legacy systems.
Sep 05 2021 03:21 PM
SolutionSince you are sorting and filtering numbers you could use
= SORT(FILTER(numbers, (numbers<60000)*(numbers>=40000)))
in Excel 365 or
= IFERROR(
SMALL(
IF(
(numbers<60000)*(numbers>=40000),
numbers
),
1+ROW()-MIN(ROW(numbers))
),
"")
in legacy systems.