Cntrl find - cannot locate zip codes that start with zero. Possibl

I have a large excel worksheet - zip codes, cities, states, area codes ... I
need to be able to sort as well as find. Problem comes in with states that
have zips starting with zero. I can get them to appear correctly as custom or
special, but not able to do cntrl-find and bring up ... I can't believe I
can't figure this out ...

Format the Zip code column as TEXT and enter the leading zero normally as
any other character.........then FIND will "find" it...........

Vaya con Dios,
Chuck, CABGx3


"jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com> wrote
in message news:8F0C74B4-4C17-464E-9C08-510B5E43A8FE@microsoft.com...
> I have a large excel worksheet - zip codes, cities, states, area codes ...
I
> need to be able to sort as well as find. Problem comes in with states that
> have zips starting with zero. I can get them to appear correctly as custom
or
> special, but not able to do cntrl-find and bring up ... I can't believe I
> can't figure this out ...

Don't use the leading 0 when you're doing your find.

Excel is trying to find the value you type in. And if you look at the formula
bar with your leading 0 zipcode cell selected, you won't see that leading 0.

jjjJackieCalifornia wrote:
>
> I have a large excel worksheet - zip codes, cities, states, area codes ... I
> need to be able to sort as well as find. Problem comes in with states that
> have zips starting with zero. I can get them to appear correctly as custom or
> special, but not able to do cntrl-find and bring up ... I can't believe I
> can't figure this out ...

--

Dave Peterson

Doesn't work ... I already have data ... thousands of entries ... can't go
back and put a zero in front of each number, but thanks ...this is a really
tricky problem.

"CLR" wrote:

> Format the Zip code column as TEXT and enter the leading zero normally as
> any other character.........then FIND will "find" it...........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com> wrote
> in message news:8F0C74B4-4C17-464E-9C08-510B5E43A8FE@microsoft.com...
> > I have a large excel worksheet - zip codes, cities, states, area codes ...
> I
> > need to be able to sort as well as find. Problem comes in with states that
> > have zips starting with zero. I can get them to appear correctly as custom
> or
> > special, but not able to do cntrl-find and bring up ... I can't believe I
> > can't figure this out ...
>
>
>

Hi,
Sorry, but that isn't a realistic solution. This document will be used by a
large number of individuals/departments ... I cannot expect that they will
all be able to "remember" to do this ... however it does work and if it were
just me I would be satisfied with the answer. However, I see it as a bandaid
to my problem. I do appreciate your answer though.
Thank you.

"Dave Peterson" wrote:

> Don't use the leading 0 when you're doing your find.
>
> Excel is trying to find the value you type in. And if you look at the formula
> bar with your leading 0 zipcode cell selected, you won't see that leading 0.
>
> jjjJackieCalifornia wrote:
> >
> > I have a large excel worksheet - zip codes, cities, states, area codes ... I
> > need to be able to sort as well as find. Problem comes in with states that
> > have zips starting with zero. I can get them to appear correctly as custom or
> > special, but not able to do cntrl-find and bring up ... I can't believe I
> > can't figure this out ...
>
> --
>
> Dave Peterson
>

You can create a mirror copy with help formulas and then copy it and paste
special as values,
I have done that with several thousands of records, to get a zip code
(assuming 5 digit) to text, use

=TEXT(Sheet1!A1,"00000")

then just drag copy across/down as long as needed, won't take long
finally just copy the help sheet and paste special as values in place or
over the old ones


--

Regards,

Peo Sjoblom



"jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com> wrote
in message news:CBED3874-8026-4624-B1F3-50A47A687E09@microsoft.com...
> Hi,
> Sorry, but that isn't a realistic solution. This document will be used by
a
> large number of individuals/departments ... I cannot expect that they will
> all be able to "remember" to do this ... however it does work and if it
were
> just me I would be satisfied with the answer. However, I see it as a
bandaid
> to my problem. I do appreciate your answer though.
> Thank you.
>
> "Dave Peterson" wrote:
>
> > Don't use the leading 0 when you're doing your find.
> >
> > Excel is trying to find the value you type in. And if you look at the
formula
> > bar with your leading 0 zipcode cell selected, you won't see that
leading 0.
> >
> > jjjJackieCalifornia wrote:
> > >
> > > I have a large excel worksheet - zip codes, cities, states, area codes
.... I
> > > need to be able to sort as well as find. Problem comes in with states
that
> > > have zips starting with zero. I can get them to appear correctly as
custom or
> > > special, but not able to do cntrl-find and bring up ... I can't
believe I
> > > can't figure this out ...
> >
> > --
> >
> > Dave Peterson
> >

Hi,
Thanks, but I need them formatted as numbers not text ...

"Peo Sjoblom" wrote:

> You can create a mirror copy with help formulas and then copy it and paste
> special as values,
> I have done that with several thousands of records, to get a zip code
> (assuming 5 digit) to text, use
>
> =TEXT(Sheet1!A1,"00000")
>
> then just drag copy across/down as long as needed, won't take long
> finally just copy the help sheet and paste special as values in place or
> over the old ones
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com> wrote
> in message news:CBED3874-8026-4624-B1F3-50A47A687E09@microsoft.com...
> > Hi,
> > Sorry, but that isn't a realistic solution. This document will be used by
> a
> > large number of individuals/departments ... I cannot expect that they will
> > all be able to "remember" to do this ... however it does work and if it
> were
> > just me I would be satisfied with the answer. However, I see it as a
> bandaid
> > to my problem. I do appreciate your answer though.
> > Thank you.
> >
> > "Dave Peterson" wrote:
> >
> > > Don't use the leading 0 when you're doing your find.
> > >
> > > Excel is trying to find the value you type in. And if you look at the
> formula
> > > bar with your leading 0 zipcode cell selected, you won't see that
> leading 0.
> > >
> > > jjjJackieCalifornia wrote:
> > > >
> > > > I have a large excel worksheet - zip codes, cities, states, area codes
> .... I
> > > > need to be able to sort as well as find. Problem comes in with states
> that
> > > > have zips starting with zero. I can get them to appear correctly as
> custom or
> > > > special, but not able to do cntrl-find and bring up ... I can't
> believe I
> > > > can't figure this out ...
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>

Give them a macro that does the find for them. Put a button from the Forms
toolbar in Row 1 of your worksheet. Freeze the window so that row 1 is always
visible.

Then assign this macro to that button:

Option Explicit
Sub myFind()
Dim FindWhat As Double
Dim FoundCell As Range
Dim myRng As Range

FindWhat = Application.InputBox("what's the code?", Type:=1)

If FindWhat = 0 Then
Exit Sub
End If

If Selection.Cells.Count = 1 Then
Set myRng = ActiveSheet.Cells
Else
Set myRng = Selection
End If

With myRng
Set FoundCell = .Cells.Find(what:=FindWhat, after:=ActiveCell, _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext)
If FoundCell Is Nothing Then
MsgBox "Not found!"
Else
FoundCell.Select
End If
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

======
My real opinion: Don't sell your users short. If you tell them how excel
works, they'll get smarter and it'll actually help with other workbooks, too.

Smarter users (usually) makes life easier for everyone.


jjjJackieCalifornia wrote:
>
> Hi,
> Sorry, but that isn't a realistic solution. This document will be used by a
> large number of individuals/departments ... I cannot expect that they will
> all be able to "remember" to do this ... however it does work and if it were
> just me I would be satisfied with the answer. However, I see it as a bandaid
> to my problem. I do appreciate your answer though.
> Thank you.
>
> "Dave Peterson" wrote:
>
> > Don't use the leading 0 when you're doing your find.
> >
> > Excel is trying to find the value you type in. And if you look at the formula
> > bar with your leading 0 zipcode cell selected, you won't see that leading 0.
> >
> > jjjJackieCalifornia wrote:
> > >
> > > I have a large excel worksheet - zip codes, cities, states, area codes ... I
> > > need to be able to sort as well as find. Problem comes in with states that
> > > have zips starting with zero. I can get them to appear correctly as custom or
> > > special, but not able to do cntrl-find and bring up ... I can't believe I
> > > can't figure this out ...
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

How about using two columns, one for the ZIP codes formatted as TEXT, (with
leading zeros which can be easily added with a helper formula), and the
second formatted as numbers so you can do whatever you intend to do with
them that you feel you can't do with them formatted as TEXT..........

Vaya con Dios,
Chuck, CABGx3



"jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com> wrote
in message news:F6F27A16-8B55-49E9-B6CE-E4CCE6056FCA@microsoft.com...
> Doesn't work ... I already have data ... thousands of entries ... can't go
> back and put a zero in front of each number, but thanks ...this is a
really
> tricky problem.
>
> "CLR" wrote:
>
> > Format the Zip code column as TEXT and enter the leading zero normally
as
> > any other character.........then FIND will "find" it...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft.com>
wrote
> > in message news:8F0C74B4-4C17-464E-9C08-510B5E43A8FE@microsoft.com...
> > > I have a large excel worksheet - zip codes, cities, states, area codes
....
> > I
> > > need to be able to sort as well as find. Problem comes in with states
that
> > > have zips starting with zero. I can get them to appear correctly as
custom
> > or
> > > special, but not able to do cntrl-find and bring up ... I can't
believe I
> > > can't figure this out ...
> >
> >
> >

Hey - thanks! I can't wait to get to the office to try it out ...
I'll reply to post to let you know how it works out. As for your other
opinion, I would normally agree with you and still do on principle, but the
way this company is set up doesn't allow me the access to people I would like
.... if we had a chat about everything that factors in I know you'd understand
why I said what I did ... as for the people I manage - freedom and
encouragement all the way ... I'm never too busy to help someone if they want
to learn something ... a little energy goes a long way with me. Yesterday I
was furiously trying to finish a report and was really in my zone of
concentration when the receptionist buzzed me to ask if I could come up to
see what she was doing in Excel. She was so excited and confident ... very
great to watch people pushing themselves to learn more ... usually just need
a bit of encouragement. Yes, I put my report aside. This is sometimes the
problem in our virtual world ... something said one way can so quickly be
perceived for something it's not. I have to admit though that I probably
would have reached the same conclusion. Ha!
Again, thank you very much ...
Jackie

"Dave Peterson" wrote:

> Give them a macro that does the find for them. Put a button from the Forms
> toolbar in Row 1 of your worksheet. Freeze the window so that row 1 is always
> visible.
>
> Then assign this macro to that button:
>
> Option Explicit
> Sub myFind()
> Dim FindWhat As Double
> Dim FoundCell As Range
> Dim myRng As Range
>
> FindWhat = Application.InputBox("what's the code?", Type:=1)
>
> If FindWhat = 0 Then
> Exit Sub
> End If
>
> If Selection.Cells.Count = 1 Then
> Set myRng = ActiveSheet.Cells
> Else
> Set myRng = Selection
> End If
>
> With myRng
> Set FoundCell = .Cells.Find(what:=FindWhat, after:=ActiveCell, _
> LookIn:=xlFormulas, lookat:=xlWhole, _
> searchorder:=xlByRows, searchdirection:=xlNext)
> If FoundCell Is Nothing Then
> MsgBox "Not found!"
> Else
> FoundCell.Select
> End If
> End With
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> ======
> My real opinion: Don't sell your users short. If you tell them how excel
> works, they'll get smarter and it'll actually help with other workbooks, too.
>
> Smarter users (usually) makes life easier for everyone.
>
>
> jjjJackieCalifornia wrote:
> >
> > Hi,
> > Sorry, but that isn't a realistic solution. This document will be used by a
> > large number of individuals/departments ... I cannot expect that they will
> > all be able to "remember" to do this ... however it does work and if it were
> > just me I would be satisfied with the answer. However, I see it as a bandaid
> > to my problem. I do appreciate your answer though.
> > Thank you.
> >
> > "Dave Peterson" wrote:
> >
> > > Don't use the leading 0 when you're doing your find.
> > >
> > > Excel is trying to find the value you type in. And if you look at the formula
> > > bar with your leading 0 zipcode cell selected, you won't see that leading 0.
> > >
> > > jjjJackieCalifornia wrote:
> > > >
> > > > I have a large excel worksheet - zip codes, cities, states, area codes ... I
> > > > need to be able to sort as well as find. Problem comes in with states that
> > > > have zips starting with zero. I can get them to appear correctly as custom or
> > > > special, but not able to do cntrl-find and bring up ... I can't believe I
> > > > can't figure this out ...
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>