eliminate extra spaces in excel cell

I have many rows in a column T in Excel that might look like this (cell T2 for example):
INSPECTED      YES                         NO                 2.  TEST ALL

I need a vb script or formula that will eliminate any extra spaces between words; so the end result should look like:

INSPECTED YES NO 2. TEST ALL

I guess the trick is that the spaces are all different between characters so =substitute does work; any help please?
   
hostbarAsked:
Who is Participating?
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
Have you tried the trim function

eg Trim(T2)

Michael
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

you can use a formula like

=trim(A1)

to remove all leading, trailing and duplicat spaces from the text. Then copy the result and use paste special - Values to paste. If you paste over the original values, you can then delete the column with the formula.

cheers, teylyn
0
 
SiddharthRoutCommented:
I think this is what you want.

Paste this in a module and run it. Please change the sheetname and the cell address accordingly.

Sub Sample()
    Do While InStr(1, Sheets("Sheet1").Range("A1").Value, Chr(13))
        Sheets("Sheet1").Range("A1").Value = Replace(Sheets("Sheet1").Range("A1").Value, Chr(13), " ")
    Loop
    
    Do While InStr(1, Sheets("Sheet1").Range("A1").Value, Chr(10))
        Sheets("Sheet1").Range("A1").Value = Replace(Sheets("Sheet1").Range("A1").Value, Chr(10), " ")
    Loop
    
    Do While InStr(1, Sheets("Sheet1").Range("A1").Value, Chr(160))
        Sheets("Sheet1").Range("A1").Value = Replace(Sheets("Sheet1").Range("A1").Value, Chr(160), " ")
    Loop
    
    Do While InStr(1, Sheets("Sheet1").Range("A1").Value, "  ")
        Sheets("Sheet1").Range("A1").Value = Replace(Sheets("Sheet1").Range("A1").Value, "  ", " ")
    Loop
End Sub

Open in new window


Sid
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SiddharthRoutConnect With a Mentor Commented:
Here I have made it short.

Sub Sample()
    Dim Rng As Range
    
    Set Rng = Sheets("Sheet1").Range("A1") '<~~ Change this accordingly
    
    With Rng
        Do While InStr(1, .Value, Chr(13))
            .Value = Replace(.Value, Chr(13), " ")
        Loop
        
        Do While InStr(1, .Value, Chr(10))
            .Value = Replace(.Value, Chr(10), " ")
        Loop
        
        Do While InStr(1, .Value, Chr(160))
            .Value = Replace(.Value, Chr(160), " ")
        Loop
        
        Do While InStr(1, .Value, "  ")
            .Value = Replace(.Value, "  ", " ")
        Loop
    End With
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Not for points hostbar but how does TRIM() answer you original question?

Sid
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Sid, how does it NOT?

>> I need a vb script or formula that will eliminate any extra spaces between words

That's what TRIM() does.
0
 
SiddharthRoutCommented:
between words????

Sid
0
 
SiddharthRoutCommented:
with Special characters in between I mean.

Sid
0
 
SiddharthRoutCommented:
Sample Attached.

Sid
Book1.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
What special characters? It's about spaces, not special characters. The fact that if you copy and paste the sample text from the question into Excel and the spaces turn up as non-breaking spaces is due to the HTML of this site. It does not mean that the original data contains nbsp characters.

In HTML, multiple spaces will not be rendered as such, but will show as one space only. Therefore, web sites like this one use the &nbsp; character, the non-breaking space, to replace spaces and ensure that more than one spaces appear on the rendered page.
0
 
SiddharthRoutCommented:
>>>The fact that if you copy and paste the sample text from the question into Excel and the spaces turn up as non-breaking spaces is due to the HTML of this site. It does not mean that the original data contains nbsp characters.

Ah I see what you mean. I just copied the text from above like you correctly guessed.

Sid
0
 
hostbarAuthor Commented:
Is this issue closed because based on my problem; =trim() did work. Sorry if I wasn't clear but =Trim() eliminated extra spaces I had based on my example, but Sid's vb is good as well, can we split the points?
0
 
SiddharthRoutCommented:
No that is ok :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.