Remove all Letters from column

[MarkCBB]

Hi there,

I need some code to remove all alpha letters from a column (A,B,C,D etc.)

this columns as values in it but there is also letters i.e. "as.....45649.00"

 

[andylawson5]

This should work:

Sub test()

Dim sh As Worksheet

Set sh = Worksheets("Sheet1")

'loop down column A
rowID = 1
While sh.Cells(rowID, 1) <> ""
    'Remove A to Z
    For i = 65 To 90
           a = Replace(sh.Cells(rowID, 1), Chr(i), "")
           sh.Cells(rowID, 1) = a
    Next i
    
    'Remove a to z
    For i = 97 To 122
           a = Replace(sh.Cells(rowID, 1), Chr(i), "")
           sh.Cells(rowID, 1) = a
    Next i
    
    rowID = rowID + 1
Wend

End Sub

 

[MarkCBB]

Yeah that works, how to 1 add "." to the code. i.e. the one cell value is "Count..... 123" when i run the code the end value is "..... 123"

I should be able to copy paste your code, I just need to know what number the point is (".")

thanks very much for your assistance.

 

[andylawson5]

I believe Chr(46) is equivalent to a full stop.

 

[pgc01]

Hi Mark

In your first example ("as.....45649.00") the number has decimals. Is that the case?

If that's the case you don't want to delete the last dot?

 

[MarkCBB]

Hi pgc01,

yes you are correcet, it did remove all the decimals, I have used the following code to get around it.

Selection.Replace What:="......", Replacement:=""
    Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

However it still leaves the 4 spaces in the cell i.e. " 1234". I have tried useing the trim formula but no luck, I also tried:

Selection.Replace What:=" ", Replacement:=""

also didnt work.

any ideas?

 

[MarkCBB]

I got it, I had to do it the long way around but here is the section of code that works.

Cells.Find(What:="Total FNB EFT").Select
Set n11 = Selection
Columns("C").Replace What:="Count......*", Replacement:=""
Selection.Offset(, 3).Select
Selection.FormulaR1C1 = "=SUBSTITUTE(RC[-1],LEFT(RC[-1],3),"""")-0"
Selection.Copy
Selection.Offset(, -1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Offset(, 1).ClearContents
Set v11 = Selection