Removing spaces at the start of a cell

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

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

Thank You !!

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

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