Filtering and Text/number problems

[Jugglertwo]

I would like to filter some different information in my lists but I'm running
into a problem.
I have a column that is our code number at work.
It has 12, 12a, 12b,12c and 1a,1b,1c, etc.
When I filter for the anything that starts with 12 it only gives me the 12.
I would like to get everything that begins with 12 so 12,12a,12b,12c. I have
the same issue with trying to get anything that begins with a 1.
I realized that when a number has a letter with it that it makes it text.
What would I do to solve this so that I could filter as I have stated above?
I tried to format everything to Text but this didn't solve it. I found out
that I could start everyting wiht a single quote and then it would interpret
all entries as text but I really don't want to manually enter the single
quote as I have over 1000 entries that I would need to modify wiht the single
quote.
Any suggestions would be greatly appreciated !
jugglertwo

 

[Tom Hutchins]

Here is one way...

Add the following user-defined function to a Visual Basic module in your
workbook:

Function GetNbr(Rng As Range) As String
GetNbr = Val(Rng.Value)
End Function

GetNbr will return 12 for 12, 12a, 12b, etc. It will return 1 for 1, 1a, 1b,
etc.
In your worksheet, find (or add) an empty column. Enter a formula to call
GetNbr, such as
=GetNbr(A2)
where A2 has the first code number. Copy the formula down through all the
rows of data. Now filter by this column instead of the original colde number
column.

If you are new to user-defined functions (macros), this link to Jon
Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[Uma Nandan]

Hi,

In MS 2007 you have multiple filter options and if you trying this in other
version please find below the options which might help u.

On the data menu select Filter.
-click the arrow button of filter and click CUSTOM
-select begins with 12 under SHOW ROWS WHERE
-Say ok.

Hope you find this useful.

Regards,
Uma

 

[Dave Peterson]

I think you have it backwards.

If you do a custom filter "begins with" 12, you won't see the numeric entry.

But if all your entries were text, you could use that "begins with" filter.

You could preformat the entire column as text, then do the data entry. (It's
not enough to just change the number format to text without reentering the
value!)

Or you could use another helper column and convert your entries to text:

=a2&""
would be sufficient to convert the number in A2 to text--and it won't hurt
anything already text. Then you could filter on this column.