J Josh in Tampa Oct 23, 2003 #1 please don't waste your time with this thread. bonehead mistake. SORRY!!!!
S steve Oct 23, 2003 #2 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...
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...
G Guest Oct 23, 2003 #3 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
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
S steve Oct 23, 2003 #4 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...
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...
