Vba code I can call from a query to reformat text

[Moss100]

Hello,

I have an address text field that is formatted like:

7 Haven Street - (Smith), Liverpool, England

I need a public function that i can use in a query to return the result as follows:

7 Haven Street, Liverpool, England

Basically I need to remove the -, the text inside brackets and the brackets.

Many thanks Mark

 

[Andrzejek]

[blue]Option Explicit

Sub test()
Dim str As String

str = "7 Haven Street - (Smith), Liverpool, England"
Debug.Print Moss100(str)

End Sub

Function Moss100(ByRef strIn As String) As String
Dim ary() As String
Dim i As Integer

ary = Split(strIn, " ")
For i = LBound(ary) To UBound(ary)
    If Left(ary(i), 1) = "(" And Right(ary(i), 2) = ")," Then
        ary(i) = ","
    End If
    If ary(i) = "-" Then ary(i) = ""
Next i

Moss100 = Join(ary, " ")
Moss100 = Replace(Moss100, "  , ", ", ")

End Function[/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Thank you so much for your help.

Your code (as always) works, but I now notice that some records dont have the "-"

If they don't have "-" they leave the brackets in.

I'm sorry that I did not check my data properly.

So

7 Haven Street (Smith), Liverpool, England

remains as:

7 Haven Street (Smith), Liverpool, England

Regards Mark

 

[Andrzejek]

...
For i = LBound(ary) To UBound(ary)
    If Left(ary(i), 1) = "(" And Right(ary(i), 2) = ")," Then
        ary(i) = ","
    End If
    If ary(i) = "-" Then ary(i) = ""[green]
    ' Add more checks to accommodate another issue(s) here[/green][red]
    If ary(i) = ...[/red]
Next i
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

On the second thought...

[blue]

Option Explicit

Sub test()

Debug.Print Moss100("7 Haven Street - (Smith), Liverpool, England")
Debug.Print Moss100("7 Haven Street (Smith), Liverpool, England")

End Sub

Function Moss100(ByRef strIn As String) As String
Dim ary() As String
Dim i As Integer

ary = Split(strIn, " ")
For i = LBound(ary) To UBound(ary)
    If Left(ary(i), 1) = "(" And Right(ary(i), 2) = ")," Then
        ary(i) = ","
    End If
    If ary(i) = "-" Then ary(i) = ""
Next i

Moss100 = Join(ary, " ")
Do While InStr(Moss100, "  ") > 0
    Moss100 = Replace(Moss100, "  ", "")
Loop
Moss100 = Replace(Moss100, " ,", ",")

End Function

[/blue]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

7 Haven Street (Smith), Liverpool, England
remains as:
7 Haven Street (Smith), Liverpool, England

With the original code, [tt]
7 Haven Street (Smith), Liverpool, England[/tt]
will give you:[tt]
7 Haven Street , Liverpool, England[/tt]
so, something is wrong on your end :-( if you are getting: [tt]
7 Haven Street [red](Smith)[/red], Liverpool, England[/tt]

You may have some other (hidden/unprintable) characters in your data...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Still not quite.

I think the & confuses the code: for example -

54 Petzzal Road - (Alex-Jones & Swan), Liverpool, Merseyside becomes
54 Petzzal Road(Alex-Jones & Swan),Liverpool,Merseyside

Thank you Mark

 

[Andrzejek]

Of course it does. As you can see, my code Splits the data by the Space and then deals with individual elements of the array created by the Split. If you need to do all of those 'exceptions' in code, you need to modify/add to the code. You will find out very quickly that it is almost impossible to cover ALL situations in code. You will end up (I predict) doing some/a lot of data modifications 'by hand'.

That's what happens when the data is not normalized,
[pre]
Street City Region
7 Haven Street - (Smith) Liverpool England
54 Petzzal Road - (Alex-Jones & Swan) Liverpool Merseyside[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Thank you very much for your help. Much appreciated and thank you for helping me.

Regards Mark.