Another Excel Conundrum

Framar

WOW Framer
Joined
Jul 24, 2001
Posts
26,384
From
Buffalo, New York, USA/Fort Erie, Ontario, Canada
I hope I can explain this! I have been adding names to my mailing list - so far so good - I can hide and unhide - do and undo!

However, in the column with Zip Codes, which I try to make a little wider than 5 digits so that there is a separation between Zip and the next column which is phone number (for my printed list) and to separate the two columns of numbers (deep breath) every time I try to make the Zip Code column wider about 20% of the Zip Codes JUMP to the right!!! The wider I make the column, the further they jump. And they do it randomly - 10 or 12 will stay put, then one will jump, etc.

So my list ends up looking like this:

14216
14221
*****14201
14222
*****14226 (only without the asterisks cause the G won't let me do it without them!)

This is driving me NUTS!!! And I don't even BEGIN to know how to phrase a search question for my 'lil paper clip buddy, so I am asking YOU!!! Any ideas???

I have tried deleting them and retyping them in the correct location, but they jump back. I have tried dragging them to the correct location but that won't work either! Arrrggghhh!!!

Thanking y'all as usual in advance.....
 

Ron Eggers

SPFG, Supreme Picture Framing God
Joined
Jul 6, 2001
Posts
16,932
From
Wisconsin
It sounds like some of your zip code entries are formatted as numbers and some as text. That will affect their default position.

You should be able to right- or left-justify the whole column but you really need to format the whole column as text (so that 9-digit zip codes with the dash will work.) If you don't do this, you'll have all sorts of fun if you ever try to sort by zip code, which is useful for mailings or just finding out where your customers are from.

I'd be a lot happier if you were using a database instead of Excel to maintain your list.
 

Ron Eggers

SPFG, Supreme Picture Framing God
Joined
Jul 6, 2001
Posts
16,932
From
Wisconsin
I just cranked up Excel and tried to recreate your conundrum and, sure-enough, the text formatted zip codes were all left-justified and the number formatted zip codes are right-formatted. The zip-codes with dashes automatically became text.

To fix this, I highlighted the whole zip code column, clicked 'format' then 'cells' and then on the 'number' tab, I chose 'text.'

Try it. You'll like it.
 

smitten

CGF II, Certified Grumble Framer Level 2
Joined
Sep 11, 2003
Posts
452
From
new england
I still don't undertand why more people don't use Acess for there cust lists? It's what it is designed for and lets you do so much more with the data.
shrug.gif
 

jvandy57

SGF, Supreme Grumble Framer
Joined
Jul 5, 2001
Posts
1,410
From
Savannah, GA
Smitten,
Most people are used to spreadsheets like Excel because Lotus 1,2,3 and such hae been around for awhile. Access and Fox Pro are used by many framers everyday that use POS software or Information Management systems yet they don't know it.

I personally love Access and use it, it even has a complete address contact database already built in.
 

Bill Henry-

SPFG, Supreme Picture Framing God
Joined
Aug 17, 2002
Posts
20,895
From
Boondock Bowerbank, ME
Business
Retired from the grind
As Ron suggested, since a Zip code is not usually a number which is used in calculations, converting the Zip numbers to text is probably in order.

Assuming your current zip code is in, say, column “M”, in a blank column set up an equation, “ = TEXT ( M1 , 0 ) ” and FILL DOWN. (The zero after the comma is necessary to keep the number an integer). Then COPY this new column and “PASTE SPECIAL (Values only)” into the old column.

This will work unless you have preceding zeros in your zip codes like, “01234” which, if it is already a number and not text, will be displayed as, “1234”. In that case set up the equation as, “= RIGHT ( “0” & TEXT (M1 , 0 ) , 5 ) “.

If the zip needs a preceding zero, the equation will add one; if not, the zero will be ignored.

COPY and PASTE SPECIAL as before.
 

Bill Henry-

SPFG, Supreme Picture Framing God
Joined
Aug 17, 2002
Posts
20,895
From
Boondock Bowerbank, ME
Business
Retired from the grind
It's not easier, but it's funner. :D

Simply re-formatting from numbers to text will not take care of the preceding zero problem, … if Mar's using zip codes from the Boston area, that is.

<font face=gillsans size=1>Yeah, yeah, I know her shop is in Buffalo!</></font>
 

Flintstone

CGF II, Certified Grumble Framer Level 2
Joined
Aug 9, 2003
Posts
255
From
setauket, ny
Why I use Excel for all my databasing needs...

Because it has the ability to save as .dbf so if I need to import the info into another program I can do so with out having a conversion nightmare.
 

Bill Henry-

SPFG, Supreme Picture Framing God
Joined
Aug 17, 2002
Posts
20,895
From
Boondock Bowerbank, ME
Business
Retired from the grind
If you had a nine digit zip code which contained a hyphen, it would have to have been entered as text.

I would check for the length of the zip code entry. If it were greater than four digits, it would be okay (maybe). If not I would add the zero i.e.

“ = IF ( LEN ( M1) > 4, TEXT ( M1 , 0 ) , “0” & TEXT ( M1, 0 ) ) “

If you had a long list and wanted to make sure that all of the zip codes were either 5 or 9 digit types, after the above, I would do a check,

“ = IF ( OR ( LEN ( M1 ) = 5 , LEN ( M1 ) = 10 ) , "" , "X" ) ”. Ten because of the nine digit zip contains the hyphen.

The “X” would only appear in those cells whose row had something other than 5 or 9 digits.

Then if you wanted to check to see if the hyphen was in the right position e.g. you had inadvertently added a zip “0123-33333”, you could check the last column

= IF ( LEN ( M1 ) = 5, "" , IF ( FIND ( "-" , M1 , 1 ) = 6 , "" , "X" ) )

You'll either get the flag or an error message if you don't have a hyphen at all.

[ 11-06-2004, 03:58 PM: Message edited by: Bill Henry ]
 

Framar

WOW Framer
Thread starter
Joined
Jul 24, 2001
Posts
26,384
From
Buffalo, New York, USA/Fort Erie, Ontario, Canada
I just thought of something...

How did I manage to enter some Zip Codes as text already and the jumping ones were not??? Could it be that the stay-in-place ones were imported from my previous address book and the jumping ones were recent entries???

To make matters worse, due to the wind around here last night, my power went out while I was working on the files and when it came back on and I got back into Excel I was REALLY happy to see all the data intact!!!

Thanks for the help!
 
Top