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

-----Original Message-----
Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

OrigVal = Left(OrigVal, (Len(OrigVal) - InStr
(OrigVal, "-")))

End If

End Function

when i use it in my worksheet, i get 0 (zero) instead of
a
trimmed zip code.

supposed to work like this.....if it finds something like:

33534-0098, it will trim it down to 33534.

any ideas? thanks in advance!!!
.

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...
--
sb
"Josh in Tampa" wrote in message
...
please don't waste your time with this thread. bonehead
mistake. SORRY!!!!

-----Original Message-----
Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

OrigVal = Left(OrigVal, (Len(OrigVal) - InStr
(OrigVal, "-")))

End If

End Function

when i use it in my worksheet, i get 0 (zero) instead of
a
trimmed zip code.

supposed to work like this.....if it finds something like:

33534-0098, it will trim it down to 33534.

any ideas? thanks in advance!!!
.

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


-----Original Message-----
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...
--
sb
"Josh in Tampa"
wrote in message
...
please don't waste your time with this thread. bonehead
mistake. SORRY!!!!

-----Original Message-----
Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

OrigVal = Left(OrigVal, (Len(OrigVal) - InStr
(OrigVal, "-")))

End If

End Function

when i use it in my worksheet, i get 0 (zero) instead
of
a
trimmed zip code.

supposed to work like this.....if it finds something
like:

33534-0098, it will trim it down to 33534.

any ideas? thanks in advance!!!
.



.

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...
--
sb
wrote in message
...
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


-----Original Message-----
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...
--
sb
"Josh in Tampa"
wrote in message
...
please don't waste your time with this thread. bonehead
mistake. SORRY!!!!

-----Original Message-----
Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

OrigVal = Left(OrigVal, (Len(OrigVal) - InStr
(OrigVal, "-")))

End If

End Function

when i use it in my worksheet, i get 0 (zero) instead
of
a
trimmed zip code.

supposed to work like this.....if it finds something
like:

33534-0098, it will trim it down to 33534.

any ideas? thanks in advance!!!
.



.