jQuery tablesorter - Not sorting column with formatted currency value

Question

jQuery 1.7.1 & tablesorter plugin - I have a currency column with thousand separators and values like $52.00 $26.70 $100.00 $50.00 $1,002.00 $1,102.00. When I try to sort getting sorted in the following way,

   $1,002.00  
   $1,102.00
   $26.70
   $50.00
   $52.00
   $100.00

Need values like,

   $26.70
   $50.00
   $52.00
   $100.00
   $1,002.00  
   $1,102.00

Tried many solutions mentioned here, but no success.

Michalmichalak

Why don't you remove the commas, then add them back in? – Tanhya

@JosephSilber Not sure how to do that. Thanks. – Michalmichalak

Check this out. It's EU mode but you'll be able to figure it out: #3404226 – Saxecoburggotha

@Michalmichalak - To remove the commas, simply use str.replace(/,/g, ''). To add them back in, see my answer here. – Tanhya

@BrentAnderson I followed that link and I found a fix, please see my comments under the accepted answer below. – Michalmichalak

Answer 1

Tablesorter allows you to define "custom parsers" for things like this.

// add parser through the tablesorter addParser method 
$.tablesorter.addParser({ 
    // set a unique id 
    id: 'thousands',
    is: function(s) { 
        // return false so this parser is not auto detected 
        return false; 
    }, 
    format: function(s) {
        // format your data for normalization 
        return s.replace('$','').replace(/,/g,'');
    }, 
    // set type, either numeric or text 
    type: 'numeric' 
}); 

$(function() {
    $("table").tablesorter({
        headers: {
            6: {//zero-based column index
                sorter:'thousands'
            }
        }
    });
});

You may have to tweak the format function, which I've not tested.

Kibler

I tested your solution, it works. Thanks. I have selected your answer as accepted. I have lot of pages with currency value like above, so I put a fix in the jquery.tablesorter.min.js under format function of ts.addParser({id:"currency" ... with s=s.replace(',', ''); as proposed here, thanks 'Brent Anderson' for this. Thanks 'Joseph Silber' for your suggestion as well. – Michalmichalak

this fails with multiple '.'s or ','s ie 9.2 (92) > 2.2.9 (229) – Hammerlock

@Blowsie, that's no great surprise as the data no longer conforms to 'thousands' formatting rules. You would need to develop a parser to specifically handle your data. – Kibler

Answer 2

If you just want to fix currency numbers (quickest):

<script type="text/javascript">
    $("table").tablesorter({
        textExtraction: function(node){ 
            // for numbers formattted like €1.000,50 e.g. Italian
            // return $(node).text().replace(/[.$£€]/g,'').replace(/,/g,'.');

            // for numbers formattted like $1,000.50 e.g. English
            return $(node).text().replace(/[,$£€]/g,'');
         }
    })
</script>

<td><span>£80,000.00</span></td>

I don't like these 3 other proposed solutions on StackOverflow:

1. 'Use custom parser and apply in the table sort init' - not reusable for lots tables
2. 'Use custom parser and apply with table cell's class' - dirty markup
3. 'Fix TableSorters currency sort in source' - hassle for future upgrades

Baecher

If you want to add "Kg" in this textExtraction creates a bug to the rest of columns when you try to filter by the letter "g". The column won't be able to filter this "g". So I would recommend the "addParser" solution from above. – Quintuplet

Answer 3

If you want to fix all data types (most flexible):

<script type="text/javascript">
    $(function() {
        $("table").tablesorter({
            textExtraction: function(node){ 
                var cell_value = $(node).text();
                var sort_value = $(node).data('value');
                return (sort_value != undefined) ? sort_value : cell_value;
            }
        })
    })
</script>

<td data-value="2008-04-01">01 Apr 2008</td>
<td>Alice</td>
<td data-value="80.00"><span>£80.00</span></td>

This has the advantage of separating display data from the sort data, more reusable.

Baecher

Answer 4

Following the same idea proposed by @Ownen, since TableSorter v2.16.0, you can use the data-text attribute directly, without the need to declare your own textExtraction function (more info here):

<td data-text="2008-04-01">01 Apr 2008</td>
<td>Alice</td>
<td data-text="80.00"><span>£80.00</span></td>

This attribute work with other widgets too, like math.

Note: in order to make it work with the output widget, you need to declare the output_dataAttrib option:

$('#table').tablesorter({
    widgets: ["output"],
    widgetOptions : {
       output_dataAttrib: 'data-text'
    }
});

Aforesaid