Filter column if it contains number in text string with leading "4"

[programsam]

Attempting to filter a column out if it contains a 10 digit number in a TEXT string with a leading "4" such as 4********.

Sample Number: 4200114091

 

[RoryA]

If that column is actually text, then using Does not begin with 4 should work.

 

[etaf]

is it a number or is it text

sumproduct() maybe what you need

filter(the array of cells you want to show, left(cell with the numbers in , 1)="4")

=FILTER(A2:B6,LEFT(A2:A6,1)="4")

Book2
	A	B	C	D	E	F
1
2	4200114091	a			4200114091	a
3	4200114091	b			4200114091	b
4	1	c			4	e
5	3	d
6	4	e
Sheet1

Cell Formulas
Range	Formula
E2:F4	E2	=FILTER(A2:B6,LEFT(A2:A6,1)="4")
Dynamic array formulas.

to only filter 10 digit numbers
=FILTER(A2:B6,(LEFT(A2:A6,1)="4")*(LEN(A2:A6)=10))

Book2
	A	B	C	D	E	F	G
1
2	4200114091	a			4200114091	a
3	4200114091	b			4200114091	b
4	1	c
5	3	d
6	4	e
Sheet1

Cell Formulas
Range	Formula
E2:F3	E2	=FILTER(A2:B6,(LEFT(A2:A6,1)="4")*(LEN(A2:A6)=10))
Dynamic array formulas.

 

[programsam]

Should have been more specific, needed it done in Power Query... this worked.

= Table.AddColumn(#"Filtered Rows", "Custom", each List.Select(Text.SplitAny([Text],Text.Remove([Text],{"0".."9"})),each Text.StartsWith(_,"4") and Text.Length(_)=10){0}?)