Fix numbers with trailing minus signs

[Clairc]

Hi,

Does any one know how to fix numbers with trailing minus signs?

For example:

from 1234- to -1,234

thanks

Clair

[apathetichell]

do you want them as numbers or strings?

tonumber(regex_replace([myfield],"(.*)(-)","$2,$1")) in a new integer field.

[Clairc]

Hi there,

thanks for your quick respond but the data preview shows "-," in front of the number, can you please advise how to fix it?

thanks,

Clair

[apathetichell]

my bad - second part should say "$2$1"

apparently my error doesn't matter when wrapping everything in a tonumber() btw.

[Clairc · Accepted solution]

Awesome! thanks so much for your help!

[apathetichell]

Glad to hear it worked! Play around with regex - it's very good for these kinds of problems. Also - if this helped can you mark the regex post as a solution? thanks!

[Clairc]

Sure, i will play around with regex. One question, how to mark the regex as a solution? Today is my first time play around this new tool.

[Clairc]

Hi there,

How come it still shows text format in the Excel output data? Anything wrong in my formula below?

[apathetichell]

? if you output data to excel it's always text. if you want numbers you have output to a formatted range or use a table tool and then render. note - render will re-write any data in your .xlsx file.

[Clairc]

Sorry, i'm new to this tool, do you mind to provide more instructions/screenshot on how to use table tool/render.note to covert text to number in the output excel file? thanks.