Spaces
 

I also need another short macro that could go through an
entire sheet deleting a space where it found it at the
beginning of a cell, but not in any other part of the
cell's contents, eg it would delete the space from

example

turning it into

example

but where it found

this example

it would only delete the space from the beginning of the
cell, not the space between the words.

This is something that is possible in Word using a
wildcard search, but not in Excel as the Find & Replace
function is different.

Could anyone help with this problem? Thank you.

Steve

Spaces
 

Dim cel As Range
For Each cel In ActiveSheet.UsedRange
If Left(cel.Value, 1) = " " Then cel.Value = Mid(cel.Value, 2)
Next

is one way, but you probably really want to use ltrim. This only gets
one at a time.

On Wed, 27 Aug 2003 03:28:58 -0700, "Steve Wylie"
wrote:

I also need another short macro that could go through an
entire sheet deleting a space where it found it at the
beginning of a cell, but not in any other part of the
cell's contents, eg it would delete the space from

example

turning it into

example

but where it found

this example

it would only delete the space from the beginning of the
cell, not the space between the words.

Spaces
 

Hi Steve

This will delete the first space from any cell on the
activesheet in the usedrange

Sub removeSpaces()
Dim rng As Range
With ActiveSheet
For Each rng In .UsedRange
If Left(rng.Text, 1) = " " Then
rng = Replace(rng.Text, " ", "")
End If
Next
End With
End Sub


-----Original Message-----
I also need another short macro that could go through an
entire sheet deleting a space where it found it at the
beginning of a cell, but not in any other part of the
cell's contents, eg it would delete the space from

example

turning it into

example

but where it found

this example

it would only delete the space from the beginning of the
cell, not the space between the words.

This is something that is possible in Word using a
wildcard search, but not in Excel as the Find & Replace
function is different.

Could anyone help with this problem? Thank you.

Steve

.

Spaces
 

That's excellent, thank you. And for your assistance
with the other macro too.

Steve

Spaces
 

On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
The VBA Trim function works differently than the Excel Trim function.
In Excel all extra spaces are removed, including those between words.

Really? Try the following in a cell
=TRIM("Arnie at ajcomputers")
or in VBA,
rng = WorksheetFunction.Trim(rng.Text)

Excel's trim does compress multiple spaces which might be useful, but at
any rate OP stated "beginning" twice so I suggested ltrim.

On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
Richard's solution is perfect.

That code won't execute for me. What version of Excel do you use?

On Wed, 27 Aug 2003 03:57:51 -0700, "Richard Daniels"
wrote:
Sub removeSpaces()
Dim rng As Range
With ActiveSheet
For Each rng In .UsedRange
If Left(rng.Text, 1) = " " Then
rng = Replace(rng.Text, " ", "")
End If
Next
End With
End Sub

Spaces
 

Wild Bill,

The code in question by Richard Daniels runs fine on Excel 2002 but
not on Excel 97.

Your comment "compress multiple spaces" and my comment "In Excel all
extra spaces are removed" may be open for interpretation. They seem
close enough to mean the same thing.

As for the TRIM function... The differences are subtle, however, I
believe the two TRIM functions operate differently. You correctly
state that "rng = WorksheetFunction.Trim(rng.Text)" works exactly
like using the TRIM function on a worksheet (removes
"extra"/"multiple" spaces). It should because you are calling a
workbook function. All I am suggesting is that the "native VBA" TRIM
function operates differently (it only removes spaces from before and
after the string, not within the string). But then, I could be
wrong...


(Wild Bill) wrote in message ...
On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
The VBA Trim function works differently than the Excel Trim function.
In Excel all extra spaces are removed, including those between words.

Really? Try the following in a cell
=TRIM("Arnie at ajcomputers")
or in VBA,
rng = WorksheetFunction.Trim(rng.Text)

Excel's trim does compress multiple spaces which might be useful, but at
any rate OP stated "beginning" twice so I suggested ltrim.

On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
Richard's solution is perfect.

That code won't execute for me. What version of Excel do you use?

On Wed, 27 Aug 2003 03:57:51 -0700, "Richard Daniels"
wrote:
Sub removeSpaces()
Dim rng As Range
With ActiveSheet
For Each rng In .UsedRange
If Left(rng.Text, 1) = " " Then
rng = Replace(rng.Text, " ", "")
End If
Next
End With
End Sub

Spaces
 

That's a very good point.

On 28 Aug 2003 08:08:14 -0700, (Arnie) wrote:

All I am suggesting is that the "native VBA" TRIM
function operates differently (it only removes spaces from before and
after the string, not within the string).

Spaces
 

Richard's code doesn't run in xl97 because he uses the Replace command,
introduced in VBA 6 (xl2000 and later).

--
Regards,
Tom Ogilvy


"Arnie" wrote in message
om...
Wild Bill,

The code in question by Richard Daniels runs fine on Excel 2002 but
not on Excel 97.

Your comment "compress multiple spaces" and my comment "In Excel all
extra spaces are removed" may be open for interpretation. They seem
close enough to mean the same thing.

As for the TRIM function... The differences are subtle, however, I
believe the two TRIM functions operate differently. You correctly
state that "rng = WorksheetFunction.Trim(rng.Text)" works exactly
like using the TRIM function on a worksheet (removes
"extra"/"multiple" spaces). It should because you are calling a
workbook function. All I am suggesting is that the "native VBA" TRIM
function operates differently (it only removes spaces from before and
after the string, not within the string). But then, I could be
wrong...


(Wild Bill) wrote in message
...
On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
The VBA Trim function works differently than the Excel Trim function.
In Excel all extra spaces are removed, including those between words.

Really? Try the following in a cell
=TRIM("Arnie at ajcomputers")
or in VBA,
rng = WorksheetFunction.Trim(rng.Text)

Excel's trim does compress multiple spaces which might be useful, but at
any rate OP stated "beginning" twice so I suggested ltrim.

On 27 Aug 2003 06:51:26 -0700, (Arnie) wrote:
Richard's solution is perfect.

That code won't execute for me. What version of Excel do you use?

On Wed, 27 Aug 2003 03:57:51 -0700, "Richard Daniels"
wrote:
Sub removeSpaces()
Dim rng As Range
With ActiveSheet
For Each rng In .UsedRange
If Left(rng.Text, 1) = " " Then
rng = Replace(rng.Text, " ", "")
End If
Next
End With
End Sub