Code to remove "#" or "APT" from Line 2 of addresses

[snipe]

Hey guys,



I wrote the following function to remove the "#" or "APT" from line 2 of an address (i.e. "APT 201" or "#201" goes to just "201").

Public Function AptName(Apt As String) As String    
    If Apt Like "APT *" Then
        AptName = Right(Apt, Len(Apt) - 4)
    Else
        If Apt Like "[#]*" Then
        AptName = Right(Apt, Len(Apt) - 1)
        Else
        AptName = Apt
        End If
    End If






End Function

That last bit, "AptName = Apt" was a recent addition to try to get rid of the #Errors I am getting.. but obviously it didn't work. The majority of the addresses in our system don't have a Line2 address. That field is null in those cases. I keep getting an #Error. Why? How can I fix this?

[ItsMe]

Just an observation and I have not tested it but it looks like you are counting characters to include white space
AptName = Right(Apt, Len(Apt) - 4)

So maybe the trim function would be more appropriate
AptName = Trim(Right(Apt, Len(Apt) - 3))

I think that's how it would go. You would have to test it.


As for the #Error
Instead of using the LIKE operator, I would probably use the InStr() function

So maybe
If InStr(AptName, "#") Then

[pbaldy]

A String can't take a Null. Change

Public Function AptName(Apt As Variant) As String

And you you may want to test for Null in the function, or have it also return Variant.

[snipe]

pbaldy for the win.


Thank you very much! Works great now!

[pbaldy]

Happy to help!