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]

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

AkaTrouble,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[Rick Rothstein]

Here is another macro that will trim all the cells in the selection, even if the selection is not contiguous...

[table="width: 500"]
[tr]
	[td]Sub TrimCellsInSelection()
  Dim Ar As Range
  For Each Ar In Selection.Areas
    Ar = Evaluate("IF(" & Ar.Address & "="""","""",TRIM(" & Ar.Address & "))")
  Next
End Sub[/td]
[/tr]
[/table]

If the selection will always be contiguous, then this simpler macro should work...

[table="width: 500"]
[tr]
	[td]Sub TrimCellsInSelection()
  Selection = Evaluate("IF(" & Selection.Address & "="""","""",TRIM(" & Selection.Address & "))")
End Sub[/td]
[/tr]
[/table]

 

[miketurn]

@hiker95
Wow, Thank You for your continued help I really appreciate it.
I have tested out the macros you provided and so far they work perfect!, exactly what I was looking for.
As @AkaTrouble mentioned, the way you created this series of macros is very helpful as a learning tool which is a very nice added bonus.


@Rick Rothstein
Thank You for providing your versions also, I tested them out and they work great as well.
Also thank you for making me learn a new word, "contigious"


@AkaTrouble
I am actually really glad you mentioned this, I agree, these are very great learning tools.
Also I always hope that my requests benefit others as well in one form or another, so it is great to hear that the help being offered to me is also helping other members.


I feel these are some great tools that not only will help me, but many others in the future.

Again Thank You guys for all your help.

 

[hiker95]

miketurn,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.