Macro for Excel 2007
Thread poster: Daniel Pestana

Daniel Pestana  Identity Verified
Portugal
Local time: 09:18
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.


Direct link Reply with quote
 

Narcis Lozano Drago  Identity Verified
Spain
Local time: 10:18
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]


Direct link Reply with quote
 

Daniel Pestana  Identity Verified
Portugal
Local time: 09:18
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]


Direct link Reply with quote
 
Oscar Villegas
Local time: 19:18
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...

????


Direct link Reply with quote
 

elena_patineuse
Local time: 11:18
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]


Direct link Reply with quote
 

tectranslate ITS GmbH
Local time: 10:18
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]


Direct link Reply with quote
 

Daniel Pestana  Identity Verified
Portugal
Local time: 09:18
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


Direct link Reply with quote
 

Daniel Pestana  Identity Verified
Portugal
Local time: 09:18
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


Direct link Reply with quote
 

Daniel Pestana  Identity Verified
Portugal
Local time: 09:18
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 Macro

Thanks for all your help Benjamin. Truly appreciate it.


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 10:18
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


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 10:18
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.

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

Antonin


Direct link Reply with quote
 


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






SDL Trados Studio 2017 Freelance
The leading translation software used by over 250,000 translators.

SDL Trados Studio 2017 helps translators increase translation productivity whilst ensuring quality. Combining translation memory, terminology management and machine translation in one simple and easy-to-use environment.

More info »
WordFinder
The words you want Anywhere, Anytime

WordFinder is the market's fastest and easiest way of finding the right word, term, translation or synonym in one or more dictionaries. In our assortment you can choose among more than 120 dictionaries in 15 languages from leading publishers.

More info »



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