0

I have a spreadsheet query bringing back results. Negative numbers are formatted as ([$$123.12]) and positive numbers are formatted as ("$$123.12").

I need to format the negative number as -123.12 and the positive number as 123.12 before being inserted into a db. What type of regex would I need to use to do that? Or, could I use ColdFusion's Replace() function..and, if so, how?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
John Eubanks
  • 61
  • 6

2 Answers2

2

We created xList as holder for the variable to simulate a looping query.

Assuming negative numbers will always contain "[" and positive numbers don't have the brackets we will loop on the list and check for "[" to format the negative numbers and then format the positive numbers for values not having brackets.

<cfset xlist = '([$$123.12]),("$$123.12")'>


<cfloop list="#xlist#" index="x">       

    <cfif FindNoCase("[", x )>
        <cfset xVal = "-" & rereplaceNoCase(x,"[^0-9.]","","all" )>
    <cfelse>    
        <cfset xVal = rereplaceNoCase(x,"[^0-9.]","","all" )>
    </cfif> 

    <cfdump var="#xVal#"><br>
</cfloop>
Vlad
  • 1,077
  • 2
  • 13
  • 27
  • Thanks for updating it. I will remove the comment now, since the issue was corrected and it no longer applies. – Leigh Mar 03 '16 at 13:09
0

Here's a slightly modified version of when I did something similar. Basically, I'm treating the bracket as a negative sign, and stripping out other irrelevant characters, like $. I wasn't clear on whether or not the parenthesis were part of your answer, in which case those would need to be stripped out, too.

<cffunction name="launderMoney">
    <cfargument name="value">

    <cfset var multiplier = 1>
    <cfset arguments.value = Replace(trim(arguments.value), '"', "", "all")>
    <cfif Find("[", arguments.value)>
        <cfset multiplier = -1>
        <cfset arguments.value = Replace(Replace(arguments.value, "[", "", "all"), "]", "", "all")>
    </cfif>

    <cfset var temp = Trim(Replace(Replace(trim(arguments.value), "$", "", "all"), ",", "", "all"))>
    <cfif isNumeric(temp)>
        <cfset temp *= multiplier>
    </cfif>

    <cfreturn temp>
</cffunction>

<p>#LaunderMoney('"$$123.12"')#</p>

<p>#LaunderMoney('[$$123.12]')#</p>
Tim Jasko
  • 1,532
  • 1
  • 8
  • 12
  • Tim, I get where you are coming from, a little. Basically, have a column that produces either a positive or a negative number...positive is ("$$123.12") and negative is ([$$123.12). Just need to strip out everything so the result is 123.12(for a positive) or -123.12 (for a negative) – John Eubanks Mar 02 '16 at 16:24
  • Did you try the code above? It looks Tim generously posted an example that does exactly that. – Leigh Mar 02 '16 at 16:44
  • Modifying this to strip out parentheses should be trivial. Vlad's answer would work just as well, and already strips them out. The main difference between the two is that mine will handle input that formats negative numbers more normally (`-123.13`, for example), and his will filter out more extraneous characters. – Tim Jasko Mar 02 '16 at 19:13
  • Tim, I am sorry that it took an eternity to get back to you on this. It works great and I appreciate the assistance. – John Eubanks May 20 '16 at 20:35