3

I have calculated column in a document library. The value for this column is a part of the files title. I'm using this formula:

=RIGHT(Title,LEN(Title)-FIND("$",Title))

The data type returned from this formula is currency (I tried number as well) with zero decimal places. It all works fine except when it comes to sorting the list based on my calculated column. I'm sorting in descending order. First the list sorts itself by five digit numbers from largest to smallest. Then the list sorts itself by six digit numbers by largest to smallest. Ex: $96,000 $92,000 $90,000 $250,000 $220,000 $200,000 etc. How can I get it to sort properly?

Ascending order takes the six digit numbers first and sorts them from smallest to largest, then it takes the five digit numbers and does the same.

  • 1
    Try enclosing it in the VALUE() function and see if that changes anything. – mannaggia Aug 19 '17 at 1:32
  • Enclosing the formula in Value() worked perfectly. =Value(RIGHT(Title,LEN(Title)-FIND("$",Title))) – Tom B. Aug 19 '17 at 16:24
  • Great job @mannaggia ! Would you mind posting this as an answer so that TomBB can accept it? This will make it easier for people with similar problems in the future. Thanks! – theChrisKent Aug 20 '17 at 20:11
2

Try enclosing it in the VALUE() function and see if that changes anything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.