Posted by Charles on May 31, 2000 7:39 AM
I wonder if you can help me.
I have an Excel spread sheet that contains data.
One of the columns contains names *but* some of the names start with a 'space' (sometimes two spaces).
I would like to write a Macro that goes down a column and simply removes any spaces that are at the start.
I have tried and tried for ages to write a Macro that does this but I've got nowhere. I know there is a button for 'absolute' and 'relative' cell references but whatever I try, it doesn't work and it's driving me up the wall. I must be doing something really silly.
Does someone have an example of a Macro that, for a column of data, removes spaces at the start. Once I have this Macro I intend to create a second Macro that will run down a column and replace any 'x' with 'y' (at the start).
Yours sincerely
Charles
Posted by Ivan Moala on June 01, 0100 2:18 AM
Charles
Have a look @ this.
I think it should do what you want BUT....not tested.
Sub CleanTextDataColumn()
Dim LastCell As String
Dim CleanWhat
Dim ColRg
LastCell = ActiveCell.Address
CleanWhat = Application.InputBox("Enter text string to clean", "Clean Text", Type:=2)
If CleanWhat = False Then End
Again:
Set ColRg = Application.InputBox("Select any cell of the Column to check", "Column Selection", Type:=8)
If ColRg Is Nothing Then End
If ColRg.Columns.Count > 1 Then MsgBox "Select ONE column only!": ColRg = "": GoTo Again
Columns(ColRg.Column).Select
Selection.Replace What:=CleanWhat, Replacement:=""
Range(LastCell).Select
MsgBox "Done!"
End Sub
Ivan
Posted by Charles on June 01, 0100 3:45 AM
Ivan
Thank you so much for your reply.
I have used your macro and it does remove spaces but unfortunately it removes the spaces that are in the middle of a cell as well.
I wonder if it could be amended
Posted by Ivan Moala on June 01, 0100 4:44 AM
Try this insteadSub CleanTextDataColumn()
Dim LastCell As String
Dim CleanWhat
Dim ColRg
Dim cell
Dim TextCells
LastCell = ActiveCell.Address
CleanWhat = Application.InputBox("Enter text string to clean", "Clean Text", Type:=2)
If CleanWhat = False Then End
Again:
Set ColRg = Application.InputBox("Select any cell of the Column to check", "Column Selection", Type:=8)
If ColRg Is Nothing Then End
If ColRg.Columns.Count > 1 Then MsgBox "Select ONE column only!": GoTo Again
Columns(ColRg.Column).Select
Set TextCells = Selection.SpecialCells(xlCellTypeConstants, 2)
For Each cell In TextCells 'Selection
If Left(cell.Text, 2) = CleanWhat Then
cell.Value = Mid(cell.Text, 3)
End If
Next cell
Range(LastCell).Select
End Sub
Posted by Charles on June 01, 0100 7:34 AM
Ivan
Thanks again for your reply, I am most grateful.
I have run this macro but unfortunately there are no changes to the data at all. If I try to remove a space or a character the data doesn't change. The space (or character selected) still remains.
I wonder if the problem might be here because when I step the macro through, this If statement is never true:-
If Left(cell.Text, 2) = CleanWhat Then
cell.Value = Mid(cell.Text, 3)
End If
Thanks again
Charles
Posted by Charles on June 06, 0100 8:00 AM
Ivan,
Thank you so much for this.
It works great, it's brill.
I really do appreciate all your work.
Thanks again
Charles
Posted by Ivan Moala on June 01, 0100 3:02 PM
Hi Charlse
will the following work ?
I think the last code was more for the 2 spaces
This one will handle any number of texts.
Ivan
Sub CleanTextDataColumn()
Dim LastCell As String
Dim CleanWhat
Dim ColRg
Dim cell
Dim TextCells
LastCell = ActiveCell.Address
CleanWhat = Application.InputBox("Enter text string to clean", "Clean Text", Type:=2)
If CleanWhat = False Then End
Again:
Set ColRg = Application.InputBox("Select any cell of the Column to check", "Column Selection", Type:=8)
If ColRg Is Nothing Then End
If ColRg.Columns.Count > 1 Then MsgBox "Select ONE column only!": GoTo Again
Columns(ColRg.Column).Select
Set TextCells = Selection.SpecialCells(xlCellTypeConstants, 2)
For Each cell In TextCells 'Selection
If Left(cell.Text, Len(CleanWhat)) = CleanWhat Then
cell.Value = Mid(cell.Text, Len(CleanWhat) + 1)
End If
Next cell
Range(LastCell).Select
End Sub