Macro for Excel 2007
Thread poster: Daniel Pestana

Daniel Pestana  Identity Verified
Portugal
Local time: 17:48
Member (2010)
English to Portuguese
+ ...
Sep 26, 2010

Hi everyone,

I was wondering if any of you knows how to create Macros in Excel. I'm still learning how to master the VBA language, but I can't get this one to work. I'm building a glossary and when I paste the entries into the cells I have several empty spaces prior to the entry itself, for example:

1 [4 empty spaces] shares
2 [6 empty spaces] debentures

I need to create a macro that manages to erase only those empty spaces before the entry, meaning, it should erase the empty spaces until the first character occurs.

Is this possible? Maybe there's a function to do this, but I couldn't find it. I tried to create a small VBA program using TRIM, but it didn't work. Example:

Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub

Thanks in advance for your help.
Best.


 

Narcis Lozano Drago  Identity Verified
Spain
Local time: 18:48
Member (2007)
English to Spanish
+ ...
One solution Sep 26, 2010

Public Sub RemoveSpaces()
Application.ActiveCell.Value = Trim(Application.ActiveCell.Text)
End Sub


This should do. If you want to remove the spaces before the word use LTrim.

Cheers,

Narcís

[Edited at 2010-09-26 20:06 GMT]


 

Daniel Pestana  Identity Verified
Portugal
Local time: 17:48
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER
Thank you for your quick reply Narcis Sep 26, 2010

Hi Narcis,

It's a pleasure to e-meet you. Your solution gave me the answer to what was wrong with my code. Thank you. I tried both codes, yours and mine, but unfortunately none of them worked. So here's how I solved the problem:

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
Next
On Error GoTo 0
End Sub

This one works fine, although it still has some bugs (you need to run the macro a couple of times until all the empty spaces are removed). The best thing about this macro is it works with a range of cells, while the other code was only for a single active cell. I truly appreciate your help. Thank you very much.

Best.

[Edited at 2010-09-26 22:37 GMT]


 

Oscar Villegas
Local time: 04:48
English to Spanish
A quick suggestion without so much research... Sep 27, 2010

How about a search and replace for any " " to ""...???

double space to nothing...

????


 

elena_patineuse
Local time: 20:48
French to Russian
+ ...
function to get rid of unecessary empty spaces Sep 27, 2010

There is a nice Excel function that eliminates all empty spaces, except for single empty spaces between the words: TRIM. It is in the list of text functions.

[Edited at 2010-09-27 08:56 GMT]


 

tectranslate ITS GmbH
Local time: 18:48
German
+ ...
I wouldn't use a macro at all... Sep 27, 2010

If you do not want to clean up excessive spaces inside the entries you can just use a formula in the cell next to the one you're trying to clean up, e.g. write "=TRIM(A1)" in cell A2 and then propagate that downwards.

Copy the resulting column to the clipboard and Paste Special as Text to receive cleaned-up entries that you can then move to the original column.

HTH,
Benjamin

P.S.: Oscar's idea is not bad, but fraught with the problem that there might be spaces missing in the end!

[Edited at 2010-09-27 10:55 GMT]


 

Daniel Pestana  Identity Verified
Portugal
Local time: 17:48
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER
The Macro I wrote does that Sep 28, 2010

oscarvil wrote:

How about a search and replace for any " " to ""...???

double space to nothing...

????


Hi Oscarvil,

Thanks for your reply. Yes, the macro I wrote does that. I use the function SUBSTITUTE.

Thanks for your help.
Best,

Daniel


 

Daniel Pestana  Identity Verified
Portugal
Local time: 17:48
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER
TRIM doesn't work on the Active Cell Sep 28, 2010

elena_patineuse wrote:

There is a nice Excel function that eliminates all empty spaces, except for single empty spaces between the words: TRIM. It is in the list of text functions.

[Edited at 2010-09-27 08:56 GMT]


Hi Elena,

Thanks for your suggestion. The purpose of the Macro is to avoid using functions on other columns (cells). If I use that function (or LTRIM) I populate another column with the same information. I want to use it on the active cells.

Thanks for your help. Truly appreciate it.

Best,
Daniel


 

Daniel Pestana  Identity Verified
Portugal
Local time: 17:48
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER
TRIM doesn't work on the Active Cell Sep 28, 2010

tectranslate wrote:

If you do not want to clean up excessive spaces inside the entries you can just use a formula in the cell next to the one you're trying to clean up, e.g. write "=TRIM(A1)" in cell A2 and then propagate that downwards.

Copy the resulting column to the clipboard and Paste Special as Text to receive cleaned-up entries that you can then move to the original column.

HTH,
Benjamin

P.S.: Oscar's idea is not bad, but fraught with the problem that there might be spaces missing in the end!

[Edited at 2010-09-27 10:55 GMT]


Hi Benjamin,

As I said to Elena, the purpose of the Macro is to avoid using functions on other columns (cells). If I use that function (or LTRIM) I populate another column with the same information. I want to use it on the active cells. It saves a little bit of time. Besides, I want to share this Macro with some colleagues that have a hard time while handling Excel. A sort of Open Source Macroicon_smile.gif

Thanks for all your help Benjamin. Truly appreciate it.


 

Antoní­n Otáhal
Local time: 18:48
Member (2005)
English to Czech
+ ...
a small modificaton Sep 28, 2010

In order to avoid repetitive running of your macro, you can use (I only show a replacement of your cycle's body) :

For Each MyCell In Selection.Cells
sstr = Trim(MyCell.Value)
oncemore:
sstr = Replace(sstr, " ", "")
wh = InStr(sstr, " ")
If wh 0 then goto oncemore
MyCell.Value = sstr
Next

That will remove all spaces. Or this variant (I write [two spaces] where you should acutally type two spaces, I am afraid html code of this forum will only show one space?):

For Each MyCell In Selection.Cells
sstr = Trim(MyCell.Value)
oncemore:
sstr = Replace(sstr, "[two spaces]", " ")
wh = InStr(sstr, "[two spaces]")
If wh 0 then goto oncemore
MyCell.Value = sstr
Next

should remove all leading and training spaces and leave only one space inside the cell. From your description, I can't see which of them you actually need/want.

Antonin


 

Antoní­n Otáhal
Local time: 18:48
Member (2005)
English to Czech
+ ...
wh 0 Sep 28, 2010

Where you see wh 0 before, there should be wh <> 0

It is tricky to pass a VBA code on this forum.icon_smile.gif

Here it is in plain text: http://dl.dropbox.com/u/3759984/spaces_excel.TXT

Antonin


 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Macro for Excel 2007

Advanced search






Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

More info »
TM-Town
Manage your TMs and Terms ... and boost your translation business

Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search