DOH!!!! I'm a BONEHEAD

[Josh in Tampa]

please don't waste your time with this thread. bonehead
mistake. SORRY!!!!

 

[steve]

Josh,

Actually - it's been a good exercise understanding InStr(), and catching all
the ins-n-outs of getting code to work.

My approach (for what it's worth) would have been dependent on length

If Len(OrigVal) = 0 Then
OrigVal = 0
ElseIf Len(OrigVal) > 5 Then
OrigVal = Left(OrigVal,5)
Else
OrigVal = OrigVal
End If

Of couse you might want to check that the final OrigVal is numeric...

 

[Guest]

sb:

this was what i came up with.....

Function TrimZip(OrigVal As Variant) As String

' check for blank entry
If Len(OrigVal) + 1 = 1 Then

' if empty, leave empty
TrimZip = OrigVal

Else

' check for zip code in which dash was
intended, but forgotten
If Len(OrigVal) = 9 And Not InStr(OrigVal, "-
") Then

' trim zip code to 5 digits
TrimZip = Left(OrigVal, 5)

' for all other zip code formats
Else

' trim zip code to 5 digits if a dash
exists
TrimZip = Split(OrigVal, "-")(0)

End If

End If

End Function

 

[steve]

Works for me...

Some thoughts:
to leave the cell blank:
TrimZip = ""
why check for "-"? Aren't you only concerned if Len > 5

I just believe in brevity...