Conditional Replacement of a Comma With a Dot in a Numeric Column

Conditional replacement of a comma with a dot in a numeric column

We could create a logical condition with as.numeric and is.na as conversion to numeric returns NA if the value is not a legitimate numeric syntax. In the example, the first and third elements have ,, so it is interpreted as character and thus gives NA. Using is.na, construct a logical vector and then apply the replacement logic with sub on the subset vector. Though, it can be done with grep as well

i1 <- is.na(as.numeric(str1))
str1[i1] <- sub(",", ".", sub("\\.", "", str1[i1]))
str1
#[1] "1000.00" "8.3"     "2900.00" "9.2" 

Note that converting to numeric will remove the trailing zero digits

as.numeric(str1)
#[1] 1000.0    8.3 2900.0    9.2

data

str1 <- c('1.000,00', 8.3, '2.900,00', 9.2)

Regex Logic - replace commas between two numbers with dots

Regex Pattern

Here is the pattern that you can use to search for the commas , between two numbers

(?<=[0-9]),(?=[0-9])

Regex Demo

Replace comma(,) with dot(.) only at particular location in the string

if your text is in cell A1:

=SUBSTITUTE(SUBSTITUTE(A1,",",".",2),",",".",3)

Convert comma's to point and as numeric, but only in a certain amount of variables

Here is a function that only replaces the commas by decimal periods and removes all other dots if all characters present are digits 0-9, dots and commas.

commas2dots <- function(x){
  if(any(grepl("[^\\.,[:digit:]]", x))){
    x
  } else {
    y <- gsub("\\.", "", x)
    tc <- textConnection(y)
    on.exit(close(tc))
    scan(tc, dec = ",", quiet = TRUE)
  }
}

lapply(df, commas2dots)
#$var0
#[1] "There, are commas"   "in the text, string"
#[3] "as,well"             "how, can"           
#[5] "i"                   "fix, this"          
#[7] "thank you"          
#
#$var1
#[1]   50   72  960 1920   50   50  960
#
#$var2
#[1]   40  742 9460 1920   50   50  960
#
#$var3
#[1]  40.0  72.0  90.0   1.3  50.0  50.0 960.0
#
#$var96
#[1]   40  742 9460 1920   50   50  960

To change the data.frame's columns:

df[] <- lapply(df, commas2dots)
df
#                 var0 var1 var2  var3 var96
#1   There, are commas   50   40  40.0    40
#2 in the text, string   72  742  72.0   742
#3             as,well  960 9460  90.0  9460
#4            how, can 1920 1920   1.3  1920
#5                   i   50   50  50.0    50
#6           fix, this   50   50  50.0    50
#7           thank you  960  960 960.0   960

Data

var0 <- c("There, are commas", "in the text, string", "as,well", "how, can", "i", "fix, this", "thank you")
var1 <- c("50,0", "72,0", "960,0", "1.920,0", "50,0", "50,0", "960,0")
var2 <- c("40,0", "742,0", "9460,0", "1.920,0", "50,0", "50,0", "960,0")
var3<- c("40,0", "72,0", "90,0", "1,30", "50,0", "50,0", "960,0")
var96 <- c("40,0", "742,0", "9460,0", "1.920,0", "50,0", "50,0", "960,0")

df <- data.frame(var0, var1, var2, var3, var96)

R - f_num, but with comma

Here is a custom alternative(see note below):

detrail <- function(num,round_dec=NULL){

   if(!is.null(round_dec)){
    num<-round(num,round_dec)
   }
   gsub("^\\d\\.",",",num)

 }
 detrail(0.1)
[1] ",1"
 detrail(1.1)
[1] ",1"

detrail(0.276,2)
[1] ",28"

NOTE:

• To read this as numeric, you'll need to change options(OutDec) to , instead of . ie options(OutDec= ","). I have not done this as I do not like changing global options.See Also
• This also removes any number that is not zero. Disable this by using 0 instead of \\d.

Automatically replace dots with commas in a Google Sheets Column with Google Script

The error occurs because .replace is a string method and can't be applied to numbers. A simple workaround would be to ensure the argument is always a string, there is a .toString() method for that.

in your code try

return [row[0].toString().replace(".", ",")];

Display number value with comma instead of dot in p-column in Angular

I had to struggle with this with an app that received all data from SAP with numbers formatted with . instead of , as well. Here is the pipe I made to solve this, it is more overhead than adding a locale id and using the native angular decimal pipe

Here is a working stackblitz example of the pipe

/**
 * @Pipe
 * @description pipe to format numeric values to argentina readable currency values
 * @input number
 * @output formatted numeric value
 */

import { Pipe, PipeTransform } from '@angular/core';

@Pipe({ 
    name: 'numberFormat'
 })
export class NumberFormatPipe implements PipeTransform {
    transform(value: any): number {
        return this.localeString(value);
    }

    missingOneDecimalCheck(nStr) {
        nStr += '';
        const x = nStr.split(',')[1];
        if (x && x.length === 1) return true;   
        return false;
    }

    missingAllDecimalsCheck(nStr) {
        nStr += '';
        const x = nStr.split(',')[1];
        if (!x) return true;    
        return false;
    }

    localeString(nStr) {
        if (nStr === '') return ''; 
        let x, x1, x2, rgx, y1, y2;
        nStr += '';
        x = nStr.split('.');
        x1 = x[0];
        x2 = x.length > 1 ? ',' + x[1] : '';
        rgx = /(\d+)(\d{3})/;
        while (rgx.test(x1)) {
            x1 = x1.replace(rgx, '$1' + '.' + '$2');
        }

        /** If value was inputed by user, it could have many decimals(up to 7)
            so we need to reformat previous x1 results */
        if (x1.indexOf(',') !== -1) {
            y1 = x1.slice(x1.lastIndexOf(',')).replace(/\./g, '');

            y2 = x1.split(',');
            x = y2[0] +  y1;
        } else {
            x = x1 + x2;
            if (this.missingOneDecimalCheck(x)) return x += '0';
            if (this.missingAllDecimalsCheck(x)) return x += ',00';
        }

        return x;
    }
}

And use it in your template like this:

{{ data[col.field] | numberFormat }}

Or in your component

constructor(private format: NumberFormatPipe) {}
...
let result = this.format.transform(some_number);

Dont forget to import and add to module declarations:

declarations: [NumberFormatPipe]

A heads up, this pipe includes some code to check decimals as well, since for my case I got values with and without decimals and in some cases up to 7 decimals so you could use it as is or edit it for your needs... but my guess is this will point you in the right direction at least.

---

Related Topics