Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

eliminate extra spaces in excel cell

Posted on 2011-05-02
13
Medium Priority
?
323 Views
Last Modified: 2012-05-11
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?
   
0
Comment
Question by:hostbar
13 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 332 total points
ID: 35509884
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
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 336 total points
ID: 35509890
Have you tried the trim function

eg Trim(T2)

Michael
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509918
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 332 total points
ID: 35509920
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509935
Not for points hostbar but how does TRIM() answer you original question?

Sid
0
 
LVL 50
ID: 35509954
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509956
between words????

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509958
with Special characters in between I mean.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509961
Sample Attached.

Sid
Book1.xls
0
 
LVL 50
ID: 35509967
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35509972
>>>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
 

Author Comment

by:hostbar
ID: 35701533
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35701613
No that is ok :)

Sid
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question