Remove Spaces till the first character in cell

[fari1]

i'm working on the data, which has all the data in column A in too many cells, and it has problem of extra spaces before the start and end of the character strings e.g

          my name is lavisha

and i want to have just my name is lavisha, without any space in the start of MY and after lavisha.
would appreciate any help on it

 

[Joe4]

As long as they are regular spaces and not special characters, the TRIM function should do the job.

=TRIM(A1)

 

[fari1]

i want to have a vba code for it plz

 

[Joe4]

Use LTRIM (for left trim) and RTRIM (for right trim) in VBA.

 

[fari1]

Sub trial()
With ActiveSheet.UsedRange
With .Cells(1, .Columns.Count + 1).EntireColumn.Resize(500, 1)
.FormulaR1C1 = "=TRIM(RC1)"
Range("A1:A50000").Value = .Value
.EntireColumn.Delete
End With
End With
End Sub

i've found a code from here, it does N/A if it doesn't find the row with text, can u amend it to last used row of excel?

 

[Joe4]

It looks like TRIM does work by itself in VBA also (maybe I was thinking of Access functions).

Here is how I would do it for all values in column A:

Sub MyRemoveSpaces()
 
    Dim cell As Range
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        cell = Trim(cell.Text)
    Next cell
    
End Sub

 

[fari1]

yes, it worked like a charm, can u pl further give me a piece of code, which may delete rows that contain numbers alongwith text, i just want to keep those cells in column A which contain text

 

[Joe4]

can u pl further give me a piece of code, which may delete rows that contain numbers alongwith text, i just want to keep those cells in column A which contain text

Since this a totally separate brand new question, it is best to post a new question for it.

The general rule of thumb is this:

1. If it is a clarification, "bump", or directly-related follow-up (dependent on the previous question), then it should be posted in the same thread as the original question.

2. If it is a brand new question that is not dependent on the previous question (it can stand on its own), it should be posted as a new question.

 

[fari1]

okay i'm posting