Removing blanks between text

[Noni]

HI all,

How can I remove blanks between words in a cell? How can I change below raw address into cleaned address? There is one blank between 123, one blank between Stre and et, 3 blanks between VIC and 21 and one blank between 21 and 30.

Raw Address	Cleaned address
12 3 Maxwell Stre et VIC 21 30	123 Maxwell Street VIC 2130

 

[Peter_SSs]

Just to confirm, the logic of my udf is to remove a space if it occurs between

• Two lower case letters
• Two upper case letters
• An upper case letter followed by a lower case letter
• Two digits
• A "/" character and anything else (following)

 

[Peter_SSs]

@Noni
You have added quite a lot of 'Likes' to the thread - does that mean that that something is actually working for you?

 

[Noni]

Just to confirm, the logic of my udf is to remove a space if it occurs between

• Two lower case letters
• Two digits
• A "/" character and anything else (following)
• Two upper case letters
• An upper case letter followed by a lower case letter

I want leading and trailing spaces to be removed as well, in case if there is any

 

[Noni]

I want leading and trailing spaces to be removed as well, in case if there is any

@Peter_SSs

 

[Peter_SSs]

I want leading and trailing spaces to be removed as well, in case if there is any

Try this. As well as leading and trailing spaces it should also remove multiple internal spaces like in your original example the double space between 56 and Spring in "56 Spring Cres QLD 8767"

Function TidySpaces(s As String) As String
  Dim RX As Object
  Dim Pat As Variant
  
  Const Patterns As String = "([a-z])( )([a-z])#(\d)( )(\d)#(\/)( )(.)#([A-Z])( )([A-Z])#([A-Z])( )([a-z])"
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  For Each Pat In Split(Patterns, "#")
    RX.Pattern = Pat
    s = Application.Trim(RX.Replace(s, "$1$3"))
  Next Pat
  TidySpaces = s
End Function