May 20 2021 11:44 AM
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
May 20 2021 12:02 PM
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
May 20 2021 10:38 PM
@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.
May 21 2021 05:07 AM
@Hans Vogelaar thanks I will give it a try.