Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default Macro to remove space at front and end of a cell

Similar to the TRIM() function but I would like to find a more automated way..

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Macro to remove space at front and end of a cell

There are two TRIM functions. In the worksheet TRIM removes leading and
trailing spaces. It also removes "extra" internal spaces.

In VBA, TRIM leaves the "internal" spaces alone. If you want to do the
automatic worksheet-style TRIM, then select the cells and run:

Sub dural()
For Each r In Selection
a = r.Address
r.Value = Evaluate("Trim(" & a & ")")
Next
End Sub

This will convert the values in-place
--
Gary''s Student - gsnu200770


"yhoy" wrote:

Similar to the TRIM() function but I would like to find a more automated way..

Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to remove space at front and end of a cell

If it is possible you may have any formulas in the selected range you mat want
to protect them from being wiped out.

Sub dural_hematoma()
For Each R In Selection
If Not R.HasFormula Then
a = R.Address
R.Value = Evaluate("Trim(" & a & ")")
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Fri, 22 Feb 2008 02:33:01 -0800, Gary''s Student
wrote:

There are two TRIM functions. In the worksheet TRIM removes leading and
trailing spaces. It also removes "extra" internal spaces.

In VBA, TRIM leaves the "internal" spaces alone. If you want to do the
automatic worksheet-style TRIM, then select the cells and run:

Sub dural()
For Each r In Selection
a = r.Address
r.Value = Evaluate("Trim(" & a & ")")
Next
End Sub

This will convert the values in-place


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to remove blank space? Daniel Charts and Charting in Excel 1 August 6th 07 08:23 AM
How to remove a space Kevin Excel Discussion (Misc queries) 5 July 20th 07 01:24 PM
How do I remove a space infront of text in a cell? GAC Excel Discussion (Misc queries) 7 October 10th 06 01:41 AM
Remove Space in Text Tian Excel Discussion (Misc queries) 5 April 6th 06 07:09 PM
Function to remove a space from text in cell WITHOUT macro?? [email protected] Excel Worksheet Functions 5 May 28th 05 02:28 AM


All times are GMT +1. The time now is 01:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"