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

[CommunityMember111481]

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

[rob_marshall · Accepted solution]

@CommunityMember111481

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

Rob

[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

[rob_marshall · Accepted solution]

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

[CommunityMember111481]

Thanks Rob, sounds good.

BR from Germany

[Dlambert09 · Accepted solution]

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