In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates.
In addition to the ribbon buttons, you can use keyboard shortcuts for copy and paste. Control-c to copy; control-v to paste. Note that you can select more than one destination cell, and Excel will repeat the copied cells when they are pasted. You can also right-mouse click to copy and paste.
If you want to use built-in code, you can use toLocaleString()
with minimumFractionDigits
.
Browser compatibility for the extended options on toLocaleString()
was limited when I first wrote this answer, but the current status looks good. If you're using Node.js, you will need to npm install
the intl
package.
var value = (100000).toLocaleString(
undefined, // leave undefined to use the visitor's browser
// locale or a string like 'en-US' to override it.
{ minimumFractionDigits: 2 }
);
console.log(value);
Number formatting varies between cultures. Unless you're doing string comparison on the output (which you shouldn't), the polite thing to do is pick undefined
and let the visitor's browser use the formatting they're most familiar with.
// Demonstrate selected international locales
var locales = [
undefined, // Your own browser
'en-US', // United States
'de-DE', // Germany
'ru-RU', // Russia
'hi-IN', // India
];
var n = 100000;
var opts = { minimumFractionDigits: 2 };
for (var i = 0; i < locales.length; i++) {
console.log(locales[i], n.toLocaleString(locales[i], opts));
}
If you are from a culture with a different format from those above, please edit this post and add your locale code.
Short solution:
var n = 1234567890;
String(n).replace(/(.)(?=(\d{3})+$)/g,'$1,')
// "1,234,567,890"
Use
num = num.toFixed(2);
Where 2 is the number of decimal places
Edit:
Here's the function to format number as you want
function formatNumber(number)
{
number = number.toFixed(2) + '';
x = number.split('.');
x1 = x[0];
x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}
Sorce: www.mredkj.com
On browsers that support the ECMAScript® 2016 Internationalization API Specification (ECMA-402), you can use an Intl.NumberFormat
instance:
var nf = Intl.NumberFormat();
var x = 42000000;
console.log(nf.format(x)); // 42,000,000 in many locales
// 42.000.000 in many other locales
if (typeof Intl === "undefined" || !Intl.NumberFormat) {
console.log("This browser doesn't support Intl.NumberFormat");
} else {
var nf = Intl.NumberFormat();
var x = 42000000;
console.log(nf.format(x)); // 42,000,000 in many locales
// 42.000.000 in many other locales
}
Due to the bugs found by JasperV — good points! — I have rewritten my old code. I guess I only ever used this for positive values with two decimal places.
Depending on what you are trying to achieve, you may want rounding or not, so here are two versions split across that divide.
I've introduced the toFixed()
method as it better handles rounding to specific decimal places accurately and is well support. It does slow things down however.
This version still detaches the decimal, but using a different method than before. The w|0
part removes the decimal. For more information on that, this is a good answer. This then leaves the remaining integer, stores it in k
and then subtracts it again from the original number, leaving the decimal by itself.
Also, if we're to take negative numbers into account, we need to while loop (skipping three digits) until we hit b
. This has been calculated to be 1 when dealing with negative numbers to avoid putting something like -,100.00
The rest of the loop is the same as before.
function formatThousandsWithRounding(n, dp){
var w = n.toFixed(dp), k = w|0, b = n < 0 ? 1 : 0,
u = Math.abs(w-k), d = (''+u.toFixed(dp)).substr(2, dp),
s = ''+k, i = s.length, r = '';
while ( (i-=3) > b ) { r = ',' + s.substr(i, 3) + r; }
return s.substr(0, i + 3) + r + (d ? '.'+d: '');
};
In the snippet below you can edit the numbers to test yourself.
function formatThousandsWithRounding(n, dp){
var w = n.toFixed(dp), k = w|0, b = n < 0 ? 1 : 0,
u = Math.abs(w-k), d = (''+u.toFixed(dp)).substr(2, dp),
s = ''+k, i = s.length, r = '';
while ( (i-=3) > b ) { r = ',' + s.substr(i, 3) + r; }
return s.substr(0, i + 3) + r + (d ? '.'+d: '');
};
var dp;
var createInput = function(v){
var inp = jQuery('<input class="input" />').val(v);
var eql = jQuery('<span> = </span>');
var out = jQuery('<div class="output" />').css('display', 'inline-block');
var row = jQuery('<div class="row" />');
row.append(inp).append(eql).append(out);
inp.keyup(function(){
out.text(formatThousandsWithRounding(Number(inp.val()), Number(dp.val())));
});
inp.keyup();
jQuery('body').append(row);
return inp;
};
jQuery(function(){
var numbers = [
0, 99.999, -1000, -1000000, 1000000.42, -1000000.57, -1000000.999
], inputs = $();
dp = jQuery('#dp');
for ( var i=0; i<numbers.length; i++ ) {
inputs = inputs.add(createInput(numbers[i]));
}
dp.on('input change', function(){
inputs.keyup();
});
});
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<input id="dp" type="range" min="0" max="5" step="1" value="2" title="number of decimal places?" />
This takes a different route and attempts to avoid mathematical calculation (as this can introduce rounding, or rounding errors). If you don't want rounding, then you are only dealing with things as a string i.e. 1000.999 converted to two decimal places will only ever be 1000.99 and not 1001.00.
This method avoids using .split()
and RegExp()
however, both of which are very slow in comparison. And whilst I learned something new from Michael's answer about toLocaleString
, I also was surprised to learn that it is — by quite a way — the slowest method out of them all (at least in Firefox and Chrome; Mac OSX).
Using lastIndexOf()
we find the possibly existent decimal point, and from there everything else is pretty much the same. Save for the padding with extra 0s where needed. This code is limited to 5 decimal places. Out of my test this was the faster method.
var formatThousandsNoRounding = function(n, dp){
var e = '', s = e+n, l = s.length, b = n < 0 ? 1 : 0,
i = s.lastIndexOf('.'), j = i == -1 ? l : i,
r = e, d = s.substr(j+1, dp);
while ( (j-=3) > b ) { r = ',' + s.substr(j, 3) + r; }
return s.substr(0, j + 3) + r +
(dp ? '.' + d + ( d.length < dp ?
('00000').substr(0, dp - d.length):e):e);
};
var formatThousandsNoRounding = function(n, dp){
var e = '', s = e+n, l = s.length, b = n < 0 ? 1 : 0,
i = s.lastIndexOf('.'), j = i == -1 ? l : i,
r = e, d = s.substr(j+1, dp);
while ( (j-=3) > b ) { r = ',' + s.substr(j, 3) + r; }
return s.substr(0, j + 3) + r +
(dp ? '.' + d + ( d.length < dp ?
('00000').substr(0, dp - d.length):e):e);
};
var dp;
var createInput = function(v){
var inp = jQuery('<input class="input" />').val(v);
var eql = jQuery('<span> = </span>');
var out = jQuery('<div class="output" />').css('display', 'inline-block');
var row = jQuery('<div class="row" />');
row.append(inp).append(eql).append(out);
inp.keyup(function(){
out.text(formatThousandsNoRounding(Number(inp.val()), Number(dp.val())));
});
inp.keyup();
jQuery('body').append(row);
return inp;
};
jQuery(function(){
var numbers = [
0, 99.999, -1000, -1000000, 1000000.42, -1000000.57, -1000000.999
], inputs = $();
dp = jQuery('#dp');
for ( var i=0; i<numbers.length; i++ ) {
inputs = inputs.add(createInput(numbers[i]));
}
dp.on('input change', function(){
inputs.keyup();
});
});
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<input id="dp" type="range" min="0" max="5" step="1" value="2" title="number of decimal places?" />
I'll update with an in-page snippet demo shortly, but for now here is a fiddle:
https://jsfiddle.net/bv2ort0a/2/
Why use RegExp for this? — don't use a hammer when a toothpick will do i.e. use string manipulation:
var formatThousands = function(n, dp){
var s = ''+(Math.floor(n)), d = n % 1, i = s.length, r = '';
while ( (i -= 3) > 0 ) { r = ',' + s.substr(i, 3) + r; }
return s.substr(0, i + 3) + r +
(d ? '.' + Math.round(d * Math.pow(10, dp || 2)) : '');
};
formatThousands( 1000000.42 );
First strip off decimal:
s = '1000000', d = ~ 0.42
Work backwards from the end of the string:
',' + '000'
',' + '000' + ',000'
Finalise by adding the leftover prefix and the decimal suffix (with rounding to dp
no. decimal points):
'1' + ',000,000' + '.42'
http://jsfiddle.net/XC3sS/
Use the Number function toFixed
and this function to add the commas.
function addCommas(nStr)
{
nStr += '';
var x = nStr.split('.');
var x1 = x[0];
var x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}
n = 10000;
r = n.toFixed(2); //10000.00
addCommas(r); // 10,000.00
http://www.mredkj.com/javascript/numberFormat.html
I think with this jQuery-numberformatter you could solve your problem.
Of course, this is assuming that you don't have problem with using jQuery in your project. Please notice that the functionality is tied to the blur event.
$("#salary").blur(function(){
$(this).parseNumber({format:"#,###.00", locale:"us"});
$(this).formatNumber({format:"#,###.00", locale:"us"});
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/gh/timdown/jshashtable/hashtable.js"></script>
<script src="https://cdn.jsdelivr.net/gh/hardhub/jquery-numberformatter/src/jquery.numberformatter.js"></script>
<input type="text" id="salary">
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With