Remove Extra Spaces Within Cells

[miketurn]

Hello Everyone,

I am looking for a macro that could do two things, if anyone is interested in creating something.
1.) Remove all extra spaces between text. Meaning there should be only one space between each word.
2.) If there is one or more spaces at the BEGINNING or at the END of a cell, remove them completely.

I have seen many variations of similar macros but honestly for some reason I can't seem to get any of them to work how I want them to.

I thought that the "Trim" syntax was designed to remove spaces, but the only uses I have seen were for specific words to have the spaces trimmed.


Couple Of IMPORTANT Pieces Of Information
A.) I would like the two items above to be part of the same macro, if possible.

B.) I am looking for the simplist macro, with the intent that I could add it to run at the end of certain already existing macros that I have.

C.) With keeping the above B.) request in mind, I am looking to add this to macros that I already have, some are based on applying a macro to an ENTIRE SPREADSHEET and others are based on SELECTION. Not sure if this would require two different pieces of code to achieve or if it can be just one piece that could be added to either type of macro?

Thank You to anyone who reads this and gives it a try.

 

[hiker95]

miketurn,

Can we see examples of the strings?

And, can we see what the results should look like?


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[FDibbins]

Why not just use a helper and use =TRIM(A1) (or, build that into your macro)

 

[Eric W]

The VBA Trim function and the worksheet Trim function work differently. As Ford noted, the worksheet Trim should do what you want. You can actually call it from within a macro like this:

    Range("A1").Value = WorksheetFunction.Trim(Range("A1").Value)

 

[hiker95]

miketurn,

How about something like this?

Sample raw data in the active worksheet:

Excel 2007
	A
1	I am looking for a macro that could do two things, if anyone is interested in creating something.
2	1.) Remove all extra spaces between text. Meaning there should be only one space between each word.
3	2.) If there is one or more spaces at the BEGINNING or at the END of a cell, remove them completely.
4
Sheet1

And, after the macro:

Excel 2007
	A
1	I am looking for a macro that could do two things, if anyone is interested in creating something.
2	1.) Remove all extra spaces between text. Meaning there should be only one space between each word.
3	2.) If there is one or more spaces at the BEGINNING or at the END of a cell, remove them completely.
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveExtraSpaces()
' hiker95, 02/17/2017, ME991831
With ActiveSheet
  With .Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveExtraSpaces macro.

 

[miketurn]

@FDibbins
Thank You for your response

@Eric W
Thank You for your response, but I was unable to get this to work, I think it is because I am looking to be able to do this anywhere, and am I right in saying that the code you provided is for A1 only?

@hiker95
Thank You for your response, I tested out the macro you provided and yes, that is it, very nice! I did however run into a couple of questions.
1.) This macro works on the entire worksheet but only on Column A, is there a way to expand this to all columns in a sheet?
This way if I add this to the end of an existing macro that applies to an ENTIRE sheet it will cover all the cells (columns and rows).
2.) Is there an easy way to make a version of this macro be based on SELECTION?
This way if I add this to the end of an existing macro that applies to a SELECTION it will cover all the cells just in that selection.


Also just a general question....
These two questions above, are things that I run into quite a bit and I never really know how to handle them.
I don't write complex macros at all, so I don't know if what I am requesting from others is easy or difficult.
When it applies, I always like when I can have the option to apply a macro to an ENTIRE sheet all at once, but also like to have a version that is based on SELECTION which allows the same macro to be applied to specific cells (any columns or rows selected I desire).
I would assume that others feel the same way, but don't see many asking for this and I also don't see many already created macros follow this, which makes me question my request.

Probably depends on the macro, but are there easy ways for a non scripter to alter macros to make them apply to ENTIRE sheets as well as to be based on SELECTION?
Or are these items more complex and that is why they are not added to all macros?

Again Thank all of you for your help

 

[hiker95]

@hiker95
Thank You for your response

miketurn,

Thanks for the feedback.

You are very welcome. Glad I could help.

is there a way to expand this to all columns in a sheet?
This way if I add this to the end of an existing macro that applies to an ENTIRE sheet it will cover all the cells (columns and rows).

In my testing, the following macro will work on the used range of the active worksheet.

Sub RemoveSpaces_UsedRange()
' hiker95, 02/17/2017, ME991831
With ActiveSheet
  With .UsedRange
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
End Sub

 

[hiker95]

but also like to have a version that is based on SELECTION which allows the same macro to be applied to specific cells (any columns or rows selected I desire).

miketurn,

I will try to have something for you tomorrow.

 

[hiker95]

but also like to have a version that is based on SELECTION which allows the same macro to be applied to specific cells (any columns or rows selected I desire).

miketurn,

In my testing, the following macro will work on the selected cells of the active worksheet.

Sub RemoveSpaces_Selection()
' hiker95, 02/18/2017, ME991831
Dim c As Range
With ActiveSheet
  For Each c In Selection
    With c
      .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
    End With
  Next c
End With
End Sub

 

[AkaTrouble]

just a quick off topic Thanks, i wasn't looking at solving the OP issue but ...

your 3 macro's clearly and easily show how to use the 3 different selection options of ranges to apply code to

all 3 have been saved to my clip collection Than You