FIlter by begins with

[nancystorey]

Hey everyone, I'm lost again!. I have lots of data (over 650k lines) that I need to filter by the first numeral so that I can apply certain formulas to that group. I can't use the "between" filter because some of my numbers are 6 digits and some are 7 digits. I have tried changing the numbers to text and using "begins with", but it returns nothing. Here is an example of my data. Thanks in advance! This forum has saved me many times!


6012088

6012089

601209

601210

601212

6012130

201202

201203

2012040

2012041

2012042

 

[Faseeh]

Hi nancystorey,


This might is a bit lengthy, but i am not sure if you could do this with wild cards,


1. Add a helper column with this formula:

=IFERROR(IF(SEARCH(Criteria,Sheet2!$A2)>=1,"Yes","No"),"No")

2. Filter this column for Yes for the set criteria.


Hope that helps.


Regards,

 

[Debraj]

Hi nancystorey,


YOu dont have to convert the NUMBER to TEXT.. Just in a separate row.. write formula as

=LEFT(TEXT(A2,"@"),2)="60"

which will vitually convert your NUMBER to TEXT, and then you can search for for Criteria.. i.e Start with "60"


Regards,

Deb

 

[nancystorey]

Thanks to both of you. I was working on Faseeh's answer, when I saw Deb's. It seems to be easier for me to understand, and so far is working great. Thanks again!

 

[shrivallabha]

To convert data to text you can also try:

=A2&""