Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete leading space
I have a column of data that has a leading blank in each cell. Does someone
have a code example that I can use to delete this leading space? I have spaces between data in these cells and just need to delete the leading space on the left hand side of each cell. Any and all replies are greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete leading space
On Jan 17, 10:54 am, CB wrote:
I have a column of data that has a leading blank in each cell. Does someone have a code example that I can use to delete this leading space? I have spaces between data in these cells and just need to delete the leading space on the left hand side of each cell. Any and all replies are greatly appreciated. In an adjacent column use this formula =Right("RefCell', Len("RefCell") - 1) That should lop off the leading blank. SteveM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete leading space
The LTrim function removes spaces on the left of a String. For example,
Sub RemoveLeadingSpace() Dim R As Range Application.EnableEvents = False On Error GoTo ErrH: If TypeOf Selection Is Excel.Range Then For Each R In Selection.Cells If R.HasFormula = False Then R.Value = LTrim(R.Value) End If Next R End If ErrH: Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "CB" wrote in message ... I have a column of data that has a leading blank in each cell. Does someone have a code example that I can use to delete this leading space? I have spaces between data in these cells and just need to delete the leading space on the left hand side of each cell. Any and all replies are greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete leading space
Is this a one time fix or something to be done repeatedly?
One time: place formula in cell and fill down. =RIGHT(A1, LEN(A1)-1) If this is going in a macro then it will work quite similar. Public Sub remove_space() Dim c As Range ' Cell For Each c In ActiveSheet.Range("A:A") If Len(c) 0 Then c = Mid(c, 2) End If Next c End Sub Cheers, Jason Lepack On Jan 17, 10:54 am, CB wrote: I have a column of data that has a leading blank in each cell. Does someone have a code example that I can use to delete this leading space? I have spaces between data in these cells and just need to delete the leading space on the left hand side of each cell. Any and all replies are greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to delete leading space
Chip,
Thanks so much. This is exactly what I needed. You have saved me much time and many key strokes. cb "Chip Pearson" wrote: The LTrim function removes spaces on the left of a String. For example, Sub RemoveLeadingSpace() Dim R As Range Application.EnableEvents = False On Error GoTo ErrH: If TypeOf Selection Is Excel.Range Then For Each R In Selection.Cells If R.HasFormula = False Then R.Value = LTrim(R.Value) End If Next R End If ErrH: Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "CB" wrote in message ... I have a column of data that has a leading blank in each cell. Does someone have a code example that I can use to delete this leading space? I have spaces between data in these cells and just need to delete the leading space on the left hand side of each cell. Any and all replies are greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Leading Space in Cell | Excel Discussion (Misc queries) | |||
leading and ending space | Excel Worksheet Functions | |||
Removing leading and trailing space... | Excel Discussion (Misc queries) | |||
how do I delete a leading space in a whole lot of cells? | Excel Discussion (Misc queries) | |||
Remove leading space | Excel Worksheet Functions |