Solved

How can I remove blank spaces in Excel?

Posted on 2002-03-21
3
4,108 Views
Last Modified: 2008-03-17
I have a very large file I imported into Excel but am having problems sorting it because many entries in cells begin with a space. How can I mass remove these unwanted spaces?
0
Comment
Question by:RAE
3 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6887205
Hi RAE,

-lets say data start at row 2
-you can insert a column in front of the offending column say column A which will then become the B column
-then add this formula =LTRIM(B2) to the new column in A2
-now copy this formula down till the end of the column to trim
-to round it up select column A completely choose copy
-then goto the second column choose paste special | only values
-then delete the first column again

be sure to make a copy first of this sheet but it will work like this

HTH:Bruintje
0
 
LVL 5

Accepted Solution

by:
jklmn earned 100 total points
ID: 6887233
Hi RAE,

If you don't mind using VBA, you can select the range with the data in your sheet and run this simple code:

Sub Trim()
For Each acell In Selection.Cells
acell.Value = LTrim(acell.Value)
Next
End Sub
0
 

Author Comment

by:RAE
ID: 6887287
Thanks very much! It worked very well. (When I tried bruintje version I kept getting a #NAME? error)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now