How to convert excel number format value to number in rails?-CodePudding

We are reading number values from excel sheet and converting to number in rails using regex

Conversion should happen as follows

$10000.00 = 10000.00
10000.00 = 10000.00
$(10000) = -10000
$10,000 = 10000
$(10,000) = -10000
-$10,000 = -10000

Currently i am using regex value.to_s.gsub(/[^0-9.-] /, '') but its missing parantheses clause. Any pointer to cover $(10000) this case

CodePudding user response：

You can take out any value between parentheses and place a - sign at the start of that string, and then remove all chars other than digits, - and .:

.sub(/\A(.*?)\(([^()]*)\)/, '-\1\2').gsub(/[^0-9.-] /, '')

See the Ruby demo:

vals = ['$10000.00', '10000.00', '$(10000)', '$10,000', '$(10,000)', '-$10,000']
puts vals.map { |m| m.sub(/\A(.*?)\(([^()]*)\)/, '-\1\2').gsub(/[^0-9.-] /, '') }

Output:

# ['10000.00', '10000.00', '-10000', '10000', '-10000', '-10000']

See the regex demo. Details:

\A - start of string
(.*?) - Group 1: any zero or more chars other than line break chars as few as possible
\( - a ( char
([^()]*) - Group 2: any zero or more chars other than ( and ) chars
\) - a ) char.

The replacement is a concatenation of a - char, Group 1 and Group 2 values.

CodePudding user response：

You can confirm the string matches the required format by attempting to match the regular expression

R = /\A-?\$?(?=\(?(?<dollars>[1-9](?:\d*|\d{0,2}(?:,\d{3})*)(?:\.\d{2})?)\)?$)(?:\k<dollars>|\(\k<dollars>\))\z/

If it does match named capture group dollars will hold the correctly formatted string with the possible exception of a leading minus sign. The minus sign is prepended to the string if the string matches the regular expression

/\A\$?[-\(]/

def reformat(str)
  m = R.match(str)
  return nil if m.nil?
  (str.match?(/\A\$?[-\(]/) ? '-' : '')   m[:dollars]
end

reformat "1000"             #=> "1000"
reformat "10000.00"         #=> "10000.00"
reformat "10,000"           #=> "10000"
reformat "-10,000.00"       #=> "-10000.00"
reformat "$10000"           #=> "10000"
reformat "$10000.00"        #=> "10000.00"
reformat "-$10,000.00"      #=> "-10000.00"
reformat "(10000)"          #=> "-10000"
reformat "$(10000)"         #=> "-10000"
reformat "$(10,000)"        #=> "-10000"
reformat "$(10,000.00)"     #=> "-10000"

reformat "10000.0"          #=> nil 
reformat "10000.000"        #=> nil
reformat "$(10,000,000.00"  #=> nil
reformat "$10,000,000.00)"  #=> nil

See Rubular and regex101.com.

The regular expression can be broken down as follows. Note the positive lookahead saves the absolute dollar amount to capture group dollars. That value is later backreferenced twice.

\A              # match beginning of string
-?              # optionally match '$'
\$?             # optionally match '$' 
(?=             # begin a positive lookahead
  \(?           # optionally match '?'
  (?<dollars>   # begin capture group 'dollars'
    [1-9]       # match a digit 0-9
    (?:         # begin a non-capture group
      \d*       # match zero or more digits
      |         # or
      \d{0,2}   # match 0-2 digits
      (?:       # begin a non-capture group
        ,       # match ','
        \d{3}   # match three digits
      )*        # end non-capture group and execute zero or more times
    )           # end non-capture group
    (?:         # begin a non-capture group
      \.\d{2}   # match '.' followed by two digits
    )?          # end non-capture group and make it optional
  )             # end capture group 'dollars          
  \)?           # optionally match ')'
  $             # match end of string
)               # end positive lookahead
(?:             # begin a non-capture group
  \k<dollars>   # match contents of group named 'dollars'
  |             # or
  \(            # match '('
  \k<dollars>   # match contents of group named 'dollars'
  \)            # match ')'
)               # end non-capture group
\z              # match end of string

If you are confident that the string will have a correct format you do not need to use a regular expression.

def reformat(str)
  str.delete('$,)').sub('(','-')
end