Excel 2007 : Macro, repeat keystroke

[Graydoggy]

Hi,
I have a number of large tables that I have imported from a website. Unfortunately the text in the left column is such that if I import/paste special as text it makes it very difficult to get the data to columns.
The data in the table has a comma if it is more than 3 chars (e.g. 27,349) and does not read as a number.
So I need to go in and remove this comma.
As the record macro does not record the actual keystrokes I know I need a bit of scrip that does something like this: move across 3 characters,backspace, enter.

I have managed to run & test some script and the closest to my solution looks like this:

With ActiveCell
If Len(.Value) > 3 Then
.Value = Left(.Value, Len(.Value) - 2)
.Offset(1, 0).Select
End If
End With
End Sub

But that just deletes the last 2 characters. I don't understand what these formulas are really saying.

Any help is appreciated!

Thanks,
Graeme

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

[Graydoggy]

Ok,
all fixed up!
Any feedback on the query perhaps?

Thanks,

Graeme

[Palmetto]

Why not use the Find and Replace feature built into Excel? No VBA required and it is very fast and easy.

Click the column header, press Ctrl + H, click the Replace tab and type a comma in "Find What" box. Leave the "Replace with" box blank.

Sub Remove_Comma()

    Columns("A:A").Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

[Graydoggy]

Thanks,
Yeah - eventually that's what I did!
So simple - but I was trying all sorts of things like *'s in the 'replace' box.