Remove initial space from column of entries

[justabc]

After copying certain data into an excel worksheet, all entries in a column begin at 2nd space of each cell which I don't want. I can manually backspace each entry to the first space of each cell. But I cannot backspace all entries at once even if I try Find then put in a space for find and then in replace leave blank. Suggestions?

 

[Joe4]

Highlight column.
Then from the Data drop down menu, select Text to Columns.
Select "Fixed Width" delimiter
Put a line after the first space, so you are parsing the data into two columns (the first just being a blank space)
Click Next
Select "Do Not Import" for the first column (blank space solumn)
Click Finish

 

[Blue Dragon]

This code will remove the leading two charecters from column A

for r = 1 to Range("A65535").End(xlUp).row
temp = cells(r, 1)
l = 1
do until right(temp, l) = temp
l = l + 1
loop
cells(r,1) = right(cells(r, 1), l-2)
next r

 

[Richie(UK)]

Hi,

How about Data / Text to Columns?

 

[Barry Katcher]

Your Find a space and Replace with null works fine for me on some test data I entered. Can you describe more clearly the type of data you have copied into your worksheet?

 

[north19701]

Sub ReplaceLead()
a = ActiveSheet.UsedRange.Rows.Count
For i = 1 To a
celllength = Len(Cells(i, 1))
orgvalue = Cells(i, 1).Value
newvalue = Right(orgvalue, celllength - 1)
Cells(i, 1) = newvalue
Next i
End Sub

I'm sure this could be done much cleaner and quicker, but here's what I came up with.

Here it is again, only shorter

Sub ReplaceLead()
For i = 1 To ActiveSheet.UsedRange.Rows.Count
Cells(i, 2).Value = Right(Cells(i, 2), Len(Cells(i, 2)) - 1)
Next i
End Sub

 

[earlyd]

You could use the trim function on another sheet to refer to the cells on your sheet.

 

[justabc]

Thanks for replies. Working for me now

 

[onesummer]

Found this very useful Joe! Thank you so much!

 

[Flicker]

another solution
you can cut the second character from your cell by use mid function. Let assume that your data in column A. Insert a blank column in B and type the following formula

=mid(A1,2,len(A1)-1)
=left(A1,len(A1)-1)