How to use RegExReplace in Google Spreadsheet

Question

I'm trying to remove beggining numbers from a column in a Google Docs spreadsheet using regex. I can't get RegExReplace function to work. This is the error I get when I run/debug the code:

Missing ) after argument list. (line 14)

This is a part of my code (line 14 is the RegExReplace function line, bolded):

regexFormat = "^[0-9]+$";
replVal = value.RegExReplace(value; regexFormat; "");  //error here

rplc.setValue(replVal);

This is the official syntax: RegExReplace( text ; regular_expression ; replacement )

Anyone knows how to use this function? Thanks!

Don't know Google Spreadsheets, but are you sure the argument separator is a semicolon (;) instead of a comma (,)? Semicolon is typically a statement terminator (like you've used it at the end of the regexFormat = statement). — Ken White
– Ken White, Commented Nov 17, 2011 at 23:34
Yes, I'm quite sure, see the official syntax: RegExReplace( text ; regular_expression ; replacement ) — conualfy
– conualfy, Commented Nov 17, 2011 at 23:42
OK. Just checking; like I said, I'm not familar with Google Spreadsheets. Thought it might be worth checking. (That's why I posted a comment and not an answer.) :) — Ken White
– Ken White, Commented Nov 17, 2011 at 23:45
@conualfy do you have a sample of the original text your trying to edit and the expected outcome? I use Regexreplace and regexextract in gs every day — Aurielle Perlmann
– Aurielle Perlmann, Commented Dec 8, 2015 at 18:01
That's not a spreadsheet formula, it's Google Apps Script, which is a variant of JavaScript. JavaScript statements are (optionally) terminated by a semicolon. So, simply put, you've terminated a statement after value;, with no closing bracket. Just a typo. — Mogsdad
– Mogsdad, Commented Jan 17, 2018 at 3:05

radicand · Accepted Answer · 2013-06-12 03:32:05Z

4

I don't know why the docs list a semicolon, but if you are doing it as a spreadsheet function, you still need to use commas. Try the following:

=REGEXREPLACE("What-A Crazy str3ng", "\W", "")

Which as expected, yields

WhatACrazystr3ng

answered Jun 12, 2013 at 3:32

radicand

6,7073 gold badges30 silver badges24 bronze badges

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

Comments

conualfy · Accepted Answer · 2011-11-19 00:46:55Z

2

I've found another solution for replacing with regexp in Google Docs Script:

var replace = '';//because we want to remove matching text
var regexp2 = new RegExp("[0-9]*[\.]*");//an example of regexp to do the job
var valcurat = value.replace(regexp2, replace);//working

As I did not find any solution for RegExReplace, I changed the method with replace(regexp, new_text). This one works.

answered Nov 19, 2011 at 0:46

conualfy

6672 gold badges9 silver badges17 bronze badges

Comments

user557597 · Accepted Answer · 2011-11-18 00:36:15Z

0

This is just a guess but if the function is Javaish, maybe there are 2 forms.
Form 1:
myvar = RegExReplace(value; regexFormat; "");
Form2:
myvar = value.RegExReplace(regexFormat; "");

answered Nov 18, 2011 at 0:36

user557597

1 Comment

conualfy Over a year ago

Just tried the one with only 2 parameters and it fails to run. TypeError: Cannot find function RegExReplace in object 6. Caracal (şi parcul Constantin Poroineanu). (line 14) It looks like some other parameter is expected.

Collectives™ on Stack Overflow

How to use RegExReplace in Google Spreadsheet

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest