CODEWITHSUNDEEP
google-apps-script
deL
deL

Reputation: 91

Replace Text in Sheet Values without Changing Formatting

I have a spreadsheet with values like these: 

8504.40.9031    9403.70.0000
8504.40.9090    9403.70.8031
8504.40.9510    9403.70.9000

I am trying to use a script to find and remove the periods (".") in each value so that the end result is this:

8504409031  9403700000
8504409090  9403708031
8504409510  9403709000

I've been trying to execute the below script to achieve this:

function appendString() {
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
var writeValues = []
for (var i = 1; i <= numRows; i++) {
  var row = []
  for (var j = 1; j <= numCols; j++) {
    var currentValue = range.getCell(i,j).getValue();
    var withString = currentValue.toString().replace(".","");
    row.push(withString)
  }
  writeValues.push(row)
}
range.setValues(writeValues)
}

I've encountered the following issues that I can't resolve and where any help is greatly appreciated:

1) the script only removes some periods, and

2) the script removes trailing zeros (I assume the script reads it as float values so removes the trailing zeroes, but I don't know how to correct this). The undesired end result I get with this script is:

850440.9031 940370
850440.909  940370.8031
850440.951  940370.9

Thanks in advance!

Upvotes: 0

Views: 983

Answers (1)

deL
deL

Reputation: 91

I'-'I 's comment answered my question. Issue was failure to use regex. Corrected var withString = currentValue.toString().replace(".",""); to var withString = currentValue.toString().replace(/\./g,""); and success.

In case anyone runs into a similar issue, I was trying to build off of this answer which did not use RegEx:

How to replace text in Google Spreadsheet using App Scripts?

Upvotes: 1

Related Questions