copy paste special values with using VBA

[excelnewbie1]

I see you can use VBA to copy the value of a formal. For those who don't know VBA can you do this using a formula. My goal is to use a worksheet template that when I insert a column of data into a cells it automatically separates the text from the number and sums the column of numbers.

Data="US $500" I use this formula to separate the number =RIGHT(H3,LEN(H3)-SEARCH(" ",H3)-1). But I need the end results to be just a number and not the result of a formula. I know how to manual copy and paste special but wanted it automatic. 

Any help would be appreciated.

thanks

[#2]

@excelnewbie1 

Here is a macro:

Sub ExtractAmounts()
    Range(Range("A3"), Range("A3").End(xlDown)).TextToColumns _
        Destination:=Range("B3"), _
        Other:=True, OtherChar:="$", _
        FieldInfo:=Array(Array(1, xlSkipColumn), Array(2, xlGeneralFormat))
End Sub

[#3]

@excelnewbie1 Alternatively, use Text-to-columns on the Data ribbon.

1: Select the column with the amounts in a text format;

2: Data, Text-to-columns;

3: Step 1 of 3, choose Delimited, press next;

4: Step 2 of 3, in the preview window, select the first column and check the "Do not import column (skip)" button. Then select the 2nd column, check the "General" button and press "Advanced...". Make sure that the comma is set as the thousand-separator. Press OK, Press Finish.

You should end up with one column of numbers. No formulae or VBA needed.

[#4]

@Hans Vogelaar  thanks I will give it a try.