﻿ Conditional Replacement of a Comma With a Dot in a Numeric Column - ITCodar

# 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.

``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 }}``

``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.