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

 

[rollis13]

Are you sure they are blanks or are they unprintable characters ? If they are blanks I see no way to have it done, how do you discern them from the blanks between 3 M or l S or t V, etc.
If they are unprintable characters (it's likely if you imported the raw addresses) you can use function 'Find & Select' / 'Replace' from menu bar. Try Copy/Pasting a character and replace it with 'nothing'.
You can eventually Record a macro for this operation and then add a loop (For/Next or Do While/Loop) on all cells with addresses.

 

[SanjayGMusafir]

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

Can you upload a sample data using XL2BB utility to check what all needs to be rectified, because every cell could carry a different challenge.

 

[Noni]

Hi, how can I get the desired cleaned address

SpaceIssueMrExcel.xlsx
	A	B
1	Raw address	Desired Cleaned Address
2	215/ 20b Lexi Dr Bella Otta VI C 9087	215/20b Lexi Dr Bella Otta VIC 9087
3	7 Winter Stre et VIC 69 78	7 Winter Street VIC 6978
4	Cnr Hunter Road QLD 3045	Cnr Hunter Road QLD 3045
5	5 6 Spring Cres QL D 8767	56 Spring Cres QLD 8767
6	12 Winter Ave TSA 987 6	12 Winter Ave TSA 9876
Sheet1

 

[Noni]

Hi, how can I get the desired cleaned address

SpaceIssueMrExcel.xlsx
	A	B
1	Raw address	Desired Cleaned Address
2	215/ 20b Lexi Dr Bella Otta VI C 9087	215/20b Lexi Dr Bella Otta VIC 9087
3	7 Winter Stre et VIC 69 78	7 Winter Street VIC 6978
4	Cnr Hunter Road QLD 3045	Cnr Hunter Road QLD 3045
5	5 6 Spring Cres QL D 8767	56 Spring Cres QLD 8767
6	12 Winter Ave TSA 987 6	12 Winter Ave TSA 9876
Sheet1

there are extra spaces that need to be removed. Since I'm dealing with hundreds of rows, I cant find and replace

 

[rollis13]

I would say that in this case XL2BB code is useless. To test if there are unprintable characters in your test a real file is needed. Use a hosting site to attach link to your uploaded file. I suggest WikiFortio, free and no registration.

 

[SanjayGMusafir]

That's why I asked for the sample data. Apparently there is no uniformity in pattern of spaces. It's going to take big effort cleaning it.

I just tried and tested the visible spaces are spaces only and not any kind of unprintable characters.

With more than 500 rows as you mentioned - If you can find some kind of pattern that would make your job somewhat easier using FIND & REPLACE.

 

[Peter_SSs]

Assuming that they are standard spaces, then you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

This sort of text manipulation though is generally not fool-proof and I expect that you will find circumstances where this function does not remove spaces that should be removed or will remove spaces that should not be removed. If that happens, provide some more example and we can see if it might be possible to tweak the code some more. Whatever the code, I think you will need to manually carefully check the results.

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 = RX.Replace(s, "$1$3")
  Next Pat
  TidySpaces = s
End Function

Noni.xlsm
	A	B
1	Raw address	Desired Cleaned Address
2	215/ 20b Lexi Dr Bella Otta VI C 9087	215/20b Lexi Dr Bella Otta VIC 9087
3	7 Winter Stre et VIC 69 78	7 Winter Street VIC 6978
4	Cnr Hunter Road QLD 3045	Cnr Hunter Road QLD 3045
5	5 6 Spring Cres QL D 8767	56 Spring Cres QLD 8767
6	12 Winter Ave TSA 987 6	12 Winter Ave TSA 9876
7	13 W inter Ave TSA 987 6	13 Winter Ave TSA 9876
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=TidySpaces(A2)

 

[SanjayGMusafir]

You, Fluff & Alex surprise me with your solution(s). More significantly how you people look at a problem.

Hats Off to you three.

 

[Noni]

Much appreciated!! I'll run the code, will get back thank You!!