On Thu, 23 Oct 2003 07:15:21 -0700, "Josh in Tampa"
wrote:

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!!!


Your function returns the value in TrimZip. Since you never set it, it
contains a 0.

Try:

Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

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

End If

End Function


--ron

Hi Josh,

Just to add to Ron's response, your function returns 0 when "-" is not
found, so you'll probably want to add a check for that. In addition, you
may want to more strongly type your function so it always returns a string.
Plus, you're not grabbing enough characters in your Left$() function.
Here's some revised code:

Function TrimZip(OrigVal As Variant) As String
Dim nPos As Integer

nPos = InStr(OrigVal, "-")

If nPos Then
TrimZip = Left$(OrigVal, (Len(OrigVal) - nPos + 1))
Else
TrimZip = OrigVal
End If
End Function

If you have Excel 2000 or newer, you can use the Split() function, which
turns it into a one-liner:

Function TrimZip(OrigVal As Variant) As String
TrimZip = Split(OrigVal, "-")(0)
End Function

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Ron Rosenfeld wrote:
On Thu, 23 Oct 2003 07:15:21 -0700, "Josh in Tampa"
wrote:

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!!!


Your function returns the value in TrimZip. Since you never set it,
it contains a 0.

Try:

Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

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

End If

End Function


--ron

i used your suggestion to use the split function, and it
works great. but whenver the zip code is blank it returns
#VALUE? the way i was doing it before it was returning a
0 (zero).....and i kind of like that better. is there a
way i can return an empty string if the zip code sent to
the function is empty?

i'm trying this, but it's not working yet:

If OrigVal = "" Then
TrimZip = OrigVal
End If
-----Original Message-----
Hi Josh,

Just to add to Ron's response, your function returns 0
when "-" is not
found, so you'll probably want to add a check for that.
In addition, you
may want to more strongly type your function so it always
returns a string.
Plus, you're not grabbing enough characters in your
Left$() function.
Here's some revised code:

Function TrimZip(OrigVal As Variant) As String
Dim nPos As Integer

nPos = InStr(OrigVal, "-")

If nPos Then
TrimZip = Left$(OrigVal, (Len(OrigVal) - nPos
+ 1))
Else
TrimZip = OrigVal
End If
End Function

If you have Excel 2000 or newer, you can use the Split()
function, which
turns it into a one-liner:

Function TrimZip(OrigVal As Variant) As String
TrimZip = Split(OrigVal, "-")(0)
End Function

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Ron Rosenfeld wrote:
On Thu, 23 Oct 2003 07:15:21 -0700, "Josh in Tampa"
wrote:

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!!!


Your function returns the value in TrimZip. Since you
never set it,
it contains a 0.

Try:

Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

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

End If

End Function


--ron

.

Function TrimZip(OrigVal As Variant) As String
if OrigVal = "" then
Trimzip = ""
else
TrimZip = Split(OrigVal, "-")(0)
end if
End Function

--
Regards,
Tom Ogilvy

wrote in message
...
i used your suggestion to use the split function, and it
works great. but whenver the zip code is blank it returns
#VALUE? the way i was doing it before it was returning a
0 (zero).....and i kind of like that better. is there a
way i can return an empty string if the zip code sent to
the function is empty?

i'm trying this, but it's not working yet:

If OrigVal = "" Then
TrimZip = OrigVal
End If
-----Original Message-----
Hi Josh,

Just to add to Ron's response, your function returns 0
when "-" is not
found, so you'll probably want to add a check for that.
In addition, you
may want to more strongly type your function so it always
returns a string.
Plus, you're not grabbing enough characters in your
Left$() function.
Here's some revised code:

Function TrimZip(OrigVal As Variant) As String
Dim nPos As Integer

nPos = InStr(OrigVal, "-")

If nPos Then
TrimZip = Left$(OrigVal, (Len(OrigVal) - nPos
+ 1))
Else
TrimZip = OrigVal
End If
End Function

If you have Excel 2000 or newer, you can use the Split()
function, which
turns it into a one-liner:

Function TrimZip(OrigVal As Variant) As String
TrimZip = Split(OrigVal, "-")(0)
End Function

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Ron Rosenfeld wrote:
On Thu, 23 Oct 2003 07:15:21 -0700, "Josh in Tampa"
wrote:

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!!!


Your function returns the value in TrimZip. Since you
never set it,
it contains a 0.

Try:

Function TrimZip(OrigVal)

If InStr(OrigVal, "-") Then

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

End If

End Function


--ron

.