If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Macro needed?
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 |
#3
|
|||
|
|||
Macro needed?
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 |
#4
|
|||
|
|||
Macro needed?
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 |
#5
|
|||
|
|||
Macro needed?
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 |
#6
|
|||
|
|||
Macro needed?
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 |
#7
|
|||
|
|||
Macro needed?
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 |
#8
|
|||
|
|||
Macro needed?
"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 ---- |
#9
|
|||
|
|||
Macro needed?
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 |
Thread Tools | |
Display Modes | |
|
|