Converting a zip format in excel 1

[omega1983]

I know excel has a feature that allows one to format a number to a desired zip format. However I am using an export report that is dumped in excel. What happens is the number comes over as a text column (ie 232262007). I want excel to read the number as 23226-2007. Yet if the number comes over as 23226, then that is they way it should read. Do I need to do some type of macro or script in excel that does a conditional format based on whether the number is a five digit or nine digit zip. Simply using the utility that is already in excel works for a nine digit number but is makes a five digit number look like this (2336-). Also would it be best to dump the excel sheet into access and format the zip first.

 

[SkipVought]

Hi,

As a programmer, you should understand the difference between NUMBERS and NUMERIC CHARACTER Strings.

That's what you are dealing with.

The 5-digit ZIP really SHOULD be a 5-digit CHARACTER STRING. But Excel is not smart enough to "convert" the number to a string.

The 5-digit format is just that -- ONLY A FORMAT. The underlying data is still a NUMBER.

So 5-digit and 9-digit ZIPS are totally incompatable. YOU will have to convert every 5-digit ZIP to a character string programatically.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[kpetursson]

you could write a macro to change the formatting of the cell depending on the number of characters in it. If there are 5, then format as zip. If there are 9, then format as zip+4.

Sub Macro2()
    Dim Col As Integer
    Dim row As Integer
    Col = 1
    row = 1
    For row = 1 To 100
        If (Len(Cells(row, Col).Value) = 5) Then
            Cells(row, Col).NumberFormat = "00000"
        ElseIf (Len(Cells(row, Col).Value) = 9) Then
            Cells(row, Col).NumberFormat = "00000-0000"
        Else
            Exit Sub
        End If
    'goto next cell
    Next row
End Sub
[\code]

Hope this helps

Kevin Petursson
--
"Everyone says quotable things everyday, but their not famous... so nobody cares."... Some Person