Clean string to be converted to numbers unique scenario

[spg9119]

Hi folks,

I need a bit of help here. Full disclosure in that I am a huge NEWBIE here.

The data I am trying to manipulate came from a PDF. The numbers, currently set up as strings, have the following problematic formats:

ex: 87334.65-

ex: 7777.98-*

ex: 8888.88*

I tried the following formula I found from another post that worked pretty well at first glance:

tonumber(replacechar(trim(regex_replace([Field_1],"^-0{1,}",'-'),"0"),"$,",""),1)

However, I have run into an issue. The numbers with "-" at the end are supposed to be negative. Is there any way to these into negative numbers while having the ones without the "-" remain positive?

[CharlieS · Accepted solution]

Hi @spg9119 

Despite your claimed "newbie" status, you seem to be off to a great start! To help you wrap this up, here's one possible solution:

IF Right(regex_replace([Field_1],'[^\d.-]',''),1)='-' THEN
tonumber(regex_replace([Field_1],'[^\d.]',''))*-1
ELSE tonumber(regex_replace([Field_1],'[^\d.]','')) ENDIF

The conditional statement tests that once the string is cleaned up (removing any character that isn't numeric, '.', or '-') and the right-most character is '-', then the output should be made as a negative number (value*-1). Otherwise, just keep the numeric and '.' characters.

[spg9119]

Thanks so much, Charlie! That worked perfectly.