How to Filter Numbers Begins with In Excel and Copy the Filtered Range to a New Sheet

How to Filter a Column with Numbers Begins with or Starts with In Excel
Generally the Number Filters are look like as follows , where we do not have any option to Filter the Numbers starts with/begins with like "12***" or "21***" , as we have in Text Filters

Number Filters :

Text Filters :

In the above Text Filters , we have the Options like < Begins with > , < Ends with >.and we dont have any such options in Number Filters. 

But , still if we want to apply the Filter on a Numeric Column to Filter the Numbers starts with "12***" or "21***" , we have to do the following things :

First select the Numeric Column on which we want to apply the Filters.
Next , Change the data Format of Column and each Cell in Selection to Text using the below Code :

Selection.NumberFormat = "@"
For Each xCell In Selection
xCell.Value = CStr(xCell.Value)
Next xCell

Exit For
End If
Next X

Now the Numeric Column has been changed as Text based Column and we can see the Text Filters on that Column.



Example :
Suppose we have a Data follows int the < Master > Tab of a Workbook as follows :

In the above data, we have a Numeric Field called < Prod_Id > , from this Field, we wish to Filter Numbers starts with "12*" or "21*" and then copy this Filtered Range to a New sheet < Filtered_Prod_Id > .
This can be done using the below Macro :

Sub Trans_Filtered_Acct()

Dim WS As Worksheet
Dim New_WS As Worksheet

Set WS = ThisWorkbook.Sheets("Master")
WS.Activate

For X = 1 To 20
'Finding the <ACCT> Column Number Dynamically
If WS.Cells(1, X) = "Prod_Id" Then
C = Cells(1, X).Column
CN = Split(Cells(, c).Address, "$")(1)

Set LastDataCell = WS.Cells.Find("*", WS.Cells(1, 1), , , xlByRows, xlPrevious)
RC = LastDataCell.Row

'Selecting the the <Prod_ID> Column
WS.Range(CN & "1:" & CN & RC).Select
Selection.NumberFormat = "@"

'Converting the <Prod_ID> Column data to Text Format
For Each xCell In Selection
xCell.Value = CStr(xCell.Value)
Next xCell

Exit For
End If

Next X

'Applying Filter on <Prod_ID> Column
WS.Range(CN & "1").Select
Selection.AutoFilter Field:=3, Criteria1:="=12*", Operator:=xlOr, Criteria2:="=21*"

'Copying the All columns data in Filtered Range
'ActiveSheet.AutoFilter.Range.Copy

(or)

'Copying the Specific columns data in Filtered Range
Set MyRange = WS.Range("A1:D" & RC).SpecialCells(xlCellTypeVisible)
MyRange.Copy

'Adding a New Sheet(Filtered_Prod_Id) and Copying the Filtered <Prod_ID> data to it.
Set New_WS = Sheets.Add(After:=Sheets(Sheets.Count))
New_WS.Name = "Filtered_Prod_Id"
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False

'Coming back to <Prod_ID> tab and clearing the Filter
WS.Activate
WS.Range("A1").Select
Selection.AutoFilter
New_WS.Activate
Set WS = Nothing
Set New_WS = Nothing

MsgBox "Filtered <Prod_ID> Data has been Copied to a New Tab ", vbOKOnly, "Job Over"


End Sub

Output :

Thanks,TAMATAM