Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Convert Text to Number

Hi to all!!!

From SAP I´m getting numbers as tex in the next format and I need to convert them into a number.  On the final image an example.

On the next lines a couple of examples and the result that I´m looking for:

Original text=  1.246.861  / Result 1246861

Original text= 193.536-  / Result -193536

Original text=  6,00-  / Result -6

Any ideas?

Labels (4)

• ### text format

1 Solution

Accepted Solutions
Creator II

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', ',' , '.')   ,   num#(    TEXT ,  '#.###', ',' , '.')

7 Replies
Creator II

try use NUM#()  FUNTION,

ALSO

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', '.' , ',')   ,   num#(    TEXT ,  '#.###', '.' , ',')

Contributor III
Author

Marco hi

I just aply your formula and it converts a 7.500 (original format) = seven thousand five hundred into 7.5 seven point five. If it´s possible I will like to conver it into 7500.

Is this possible?

MVP

Like this:

num(money#([Volumen ventas],'#.##0,00;#.##0,00-', ',', '.'),'0')

num(money#([Costo de ventas]','#.##0;#.##0-'),'0')

num(num#([Ingresos],'0', ',', '.') )

Creator II

if( RIGHT(TEXT, 1 ) = '-' ,       num#(     SubField(TEXT, '-' , 1)  ,  '#.###', ',' , '.')   ,   num#(    TEXT ,  '#.###', ',' , '.')

Contributor III
Author

Marcos Thank you very Much!!!!!

MVP
Does this solution handle negative values @Marcos_rv ?

By the looks of it I would dine that you would need to multiply the first
part with -1 or am I missing something?

if( RIGHT(TEXT, 1 ) = '-' ,
-1 * num#( SubField(TEXT, '-' , 1) , '#.###', ',' , '.') ,
num#( TEXT , '#.###', ',' , '.')
)
Creator II

yes, it is that the value that is entered is a string of characters (text type), but for the sum you need to pass it to number, also the final character is the one that decides if it is negative or not. it is true the correction you mention, you need to multiply by -1 in the case that it is negative. thank you for the correction. Regards!!!