Filter Issue

[MikeDBMan]

I filter on account numbers. The data is imported from various sources. I have one account number, 5021.142. It is actually recorded in the cell as a value. I also have other account numbers 5021.142.1234. This, obviously is text. If I filter on accounts that begin with 5021 I only get the text account. I can think of no workaround other than ensuring all accounts are text, which would be a bit of a pain as I import the data and thus would have to write a macro and remember to run it every time I import data, which can be dozens of times a day. Suggestions?

When I say import, my accounting system dumps the data into excel so I don't have the luxury of using the import wizard which would allow me to make a particular column text.

 

[Desu Nota from Columbus]

I don't know how to do it without a macro.

Perhaps add a macro button to your toolbar to make it quick and easy to perform multiple times.

This macro assumes Account Numbers are in Column A, and there is a header in A1.

Sub NumToText()
Dim lrow As Long

Application.ScreenUpdating = False

lrow = Range("A" & Rows.Count).End(xlUp).Row
    Columns("B:B").Insert Shift:=xlToRight
    Range("B2:B" & lrow).Formula = "=IF(A2="""","""",TEXT(A2, ""GENERAL""))"
    Columns("B:B").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("B:B").Delete Shift:=xlToLeft

Application.ScreenUpdating = True

End Sub