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

Highlighted
Occasional Contributor

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

5 REPLIES 5
Highlighted
Master Anaplanner/Community Boss

Re: Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt)

@CommunityMember111481 

 

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

 

Rob

Highlighted
Occasional Contributor

Re: Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt)

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

Highlighted
Master Anaplanner/Community Boss

Re: Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt)

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

Highlighted
Occasional Contributor

Re: Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt)

Thanks Rob, sounds good. 

BR from Germany

Highlighted
Certified Master Anaplanner

Re: Conversion Number as Text like "1,234" to Number over VALUE Function -> VALUE(txt)

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