Replace Text in Sheet Values without Changing Formatting

Question

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!

Review the syntax of the JavaScript method String#replace on your preferred JavaScript language reference to help yourself understand the current behavior. — tehhowch
– tehhowch, Commented Jul 16, 2018 at 19:31

deL · Accepted Answer · 2018-07-16 20:42:13Z

1

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?

answered Jul 16, 2018 at 20:42

deL

911 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace Text in Sheet Values without Changing Formatting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related