Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces
That's excellent, thank you. And for your assistance
with the other macro too. Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Spaces | Excel Worksheet Functions | |||
Adding Spaces to the end of a value | Excel Worksheet Functions | |||
spaces not recognized as spaces | Excel Worksheet Functions | |||
help with spaces in funtions | Excel Worksheet Functions |