VBA Button to clear column of text and leave just numbers

[daltendavis]

I am pasting into column A row 1 and need a macro I can apply to a button that takes out all text from what has been pasted and leaves every row with the same digits just without text.

Often times there will be up to 75 rows pasted in column A.

I need a macro that I can apply to a button to just pull the text out of the numbers leaving them in the same location on the sheet so I can then click another button which I have set up to value the numbers in the column.

Thank you

 

[Rick Rothstein]

This is what I want it to do but can it export the results to sheet 2 of the workbook rather than just replacing where I pasted the data. Thank you very much

Sure, we just need to add the sheet reference to the last two lines of code...

[table="width: 500"]
[tr]
	[td]Sub NumbersAndFractionalAmounts()
  Dim R As Long, X As Long, Fract As Double
  Dim Combo As String, Txt As String, Vals() As String
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    Txt = Cells(R, "A").Value
    For X = 1 To Len(Txt)
      If Mid(Txt, X, 1) Like "[!0-9 ]" Then Mid(Txt, X) = " "
    Next
    Txt = Application.Trim(Txt)
    If Len(Txt) Then
      Fract = Format(1 / (Len(Txt) - Len(Replace(Txt, " ", "")) + 1), ".##")
      Txt = Replace(Txt, " ", "|" & Fract & " ") & "|" & Fract
    End If
    Combo = Combo & " " & Txt
  Next
  Vals = Split(Application.Trim(Combo))
  [B][COLOR="#FF0000"]Sheets("Sheet2").[/COLOR][/B]Range("A1").Resize(UBound(Vals) + 1) = Application.Transpose(Vals)
  [B][COLOR="#FF0000"]Sheets("Sheet2").[/COLOR][/B]Columns("A").TextToColumns , xlDelimited, , , False, False, False, False, True, "|", Array(Array(1, 2), Array(2, 1))
End Sub[/td]
[/tr]
[/table]