Help with Formula

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,403
Office Version
  1. 2016
Platform
  1. Windows
I think it is a IF statement I need. I have a column of cells with letters and numbers in. I need to filter out any numbers that have 46 together as the fifth and sixth digits in that cell. The column stretches down 50000 rows.

I can then sort by True and delete all the others. Please see below for example.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>ABC14567</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>ABC14678</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>ABC44444</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>ABC24678</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE>


Thanks

Excel 2007
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,403
Office Version
  1. 2016
Platform
  1. Windows
Thanks VoG worked a treat, easy when you know how! So the A1 is the cell, 5 is the start number, what is the 2?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
2 is the number of characters - so in this case it looks at the 5th and 6th characters.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,358
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top