Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt) w/o NaN

Hi, is there a good best practice to solve Text Conversion "1,234" to a number over Value(txt) giving finally NaN because "," will not be recognized by VALUE(txt). Numbers are given in text format by client as "1,234". Or is it easier to re-format the numbers in the uploadfile...by the client? Thanks in advance for a short feedback. When Numbers are given in text format within thousands separator comma, then comma is not recognized.

Is there a good way to solve the given forlmula constraints within VALUE function.

Thx

Best Answers

  • @CommunityMember111481 

     

    You should load the number data as number, not text.  Try to stay away from text as much as possible.

     

    Rob

  • @CommunityMember111481 

     

    You could use the Substitute function (link), but it would be better to just load the data in as numbers and not make HyperBlock do extra work.

  • Hello, 

     

    This kind of modification should be done before loading in Anaplan because Text format will use more space and Number format will be more efficient in term of performance. This is the recommandation of Planual. Once that said, you can use Subsitute formula to change all "," by "." and you will not have NaN issues :)

     

    Have a nice day

Answers

  • Hi Rob,
    yes, it is better to load numbers in txt to line items with format number. 
    There was also effect with the regional settings of the client files. 
    But I stay away from loading numbers as text. As I see there is no technical way to oversteer the occurence that comma is not recognized when using Value(txt) if txt = "1,234"
    BR / Andreas

  • Thanks Rob, sounds good. 

    BR from Germany