Macro needed?

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

Take a look at David McRitchie's TrimALL macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall




In article ,
"Continental Translations" wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

Try a helper cell with a formula like:

=trim(a1)



Continental Translations wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

--

Dave Peterson

Oops. You wanted a macro.



Dave Peterson wrote:

Try a helper cell with a formula like:

=trim(a1)

Continental Translations wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

--

Dave Peterson


--

Dave Peterson

For one cell.......=TRIM(A1)

For a whgole whack of cells............

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

On Tue, 15 Jun 2004 02:30:52 +0000 (UTC), "Continental Translations"
wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

What the hell is a whogle?



On Mon, 14 Jun 2004 20:22:25 -0700, Gord Dibben gorddibbATshawDOTca wrote:

For one cell.......=TRIM(A1)

For a whgole whack of cells............

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

On Tue, 15 Jun 2004 02:30:52 +0000 (UTC), "Continental Translations"
wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

Or should that what the hell is a wghole?

Gord

On Mon, 14 Jun 2004 21:08:47 -0700, Gord Dibben gorddibbATshawDOTca wrote:

What the hell is a whogle?



On Mon, 14 Jun 2004 20:22:25 -0700, Gord Dibben gorddibbATshawDOTca wrote:

For one cell.......=TRIM(A1)

For a whgole whack of cells............

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

On Tue, 15 Jun 2004 02:30:52 +0000 (UTC), "Continental Translations"
wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks

"Gord Dibben" gorddibbATshawDOTca wrote
Or should that (be) what the hell is a wghole?

One guess: wghole = whooping gigantic whole vbg

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----

I think it was Carnac who asked this question first:

Where does a wg go when it's scared? Down its wghole.

(other questions arose, but they were editted!)

Gord Dibben wrote:

Or should that what the hell is a wghole?

Gord

On Mon, 14 Jun 2004 21:08:47 -0700, Gord Dibben gorddibbATshawDOTca wrote:

What the hell is a whogle?



On Mon, 14 Jun 2004 20:22:25 -0700, Gord Dibben gorddibbATshawDOTca wrote:

For one cell.......=TRIM(A1)

For a whgole whack of cells............

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

On Tue, 15 Jun 2004 02:30:52 +0000 (UTC), "Continental Translations"
wrote:

I am after something which will delete excess spaces in certain cells. In
some of my cells, I have a couple of words. Some of these words however have
2 spaces or 3 spaces in between each word instead of 1. Is there anyway I
can change this so that all words within cells have one space inbetween them
and no spaces before the first word of the cell?

Thanks



--

Dave Peterson