Convert Currency with Commas into Numeric

Convert currency with commas into numeric

The reason why the gsub didn't work was there was , in the column, which is still non-numeric. So when convert to 'numeric' with as.numeric, all the non-numeric elements are converted to NA. So, we need to remove both , and $ to make it work.

df1$COL1 <- as.numeric(gsub('[$,]', '', df1$COL1))

We match the $ and , inside the square brackets ([$,]) so that it will be considered as that character ($ left alone has special meaning i.e. it signifies the end of the string.) and replace it with ''.

Or we can escape (\\) the character ($) to match it and replace by ''.

df1$COL1 <- as.numeric(gsub('\\$|,', '', df1$COL1))

FreeMarker convert string with commas to number

Given the limitations like locale specific numbers where decimal and thousands separator characters can either be a dot or a comma, and since I only needed to display the value and not do any numeric calculations based on the number value, I resolved this by simply ignoring the last 3 characters of the string using substring. This will remove the decimal separator and 2 decimal places (assumption being that there are always 2 decimal places).

How do I convert a number to a numeric, comma-separated formatted string?

For SQL Server 2012, or later, an easier solution is to use FORMAT ()^{Documentation}.

EG:

SELECT Format(1234567.8, '##,##0')

Results in: 1,234,568

as.numeric with comma decimal separators?

as.numeric(sub(",", ".", Input, fixed = TRUE))

should work.

How can I convert a currency string to a number ignoring separators?

If your intention is to be lenient on the placements of the grouping
separator (which can be a comma or a period, depending on the locale)
then you could remove the formatters currencyGroupingSeparator
from the input string instead of a hard-coded comma:

var input = "£10,00"
if let sep = currencyFormatter.currencyGroupingSeparator {
    input = input.replacingOccurrences(of: sep, with: "")
}

How can I parse a string with a comma thousand separator to a number?

Yes remove the commas:

let output = parseFloat("2,299.00".replace(/,/g, ''));
console.log(output);

.NET String.Format() to add commas in thousands place for a number

String.Format("{0:n}", 1234);  // Output: 1,234.00
String.Format("{0:n0}", 9876); // No digits after the decimal point. Output: 9,876

How to make a number with proper commas round to exactly 2 decimal places?

You can do it like this

var formatter = new Intl.NumberFormat('en-US', {
  style: 'currency',
  currency: 'USD',
});
var num1 = 1000000.3
console.log(formatter.format(num1))

How to read data when some numbers contain commas as thousand separator?

I want to use R rather than pre-processing the data as it makes it easier when the data are revised. Following Shane's suggestion of using gsub, I think this is about as neat as I can do:

x <- read.csv("file.csv",header=TRUE,colClasses="character")
col2cvt <- 15:41
x[,col2cvt] <- lapply(x[,col2cvt],function(x){as.numeric(gsub(",", "", x))})

Convert Currency with Commas into Numeric