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

CommunityMember111481
CommunityMember111481
in Modeling

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

Tagged:

Best Answers

  • rob_marshall
    rob_marshall
    Answer ✓

    @CommunityMember111481 

     

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

     

    Rob

  • rob_marshall
    rob_marshall
    Answer ✓

    @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.

  • Dlambert09
    Dlambert09
    Answer ✓

    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

  • CommunityMember111481
    CommunityMember111481

    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

  • CommunityMember111481
    CommunityMember111481

    Thanks Rob, sounds good. 

    BR from Germany

Categories