Office 365 Script - Replace Question

(0) Share

Report

Posted on by smithmrk

288

Hello all.

I need some help with an Excel Online Script. I need to go through all the cells in a column and replace special characters with nothing. For example for every cell in the column, I need to replace a "*" with "", and "-", with "".

It works fine...however when running the script in Power Automate Flow it times out. Even without using it in Power Automate it is slow...so I'm wondering is there a way to filter the column and only provide the cells that have a special character in them?

Give me all the cells that have a "*" or "-" and that way I only have to update those cells...or can I do some kind of BULK replace the entire column where there is a "*" or "-"?

Thanks in advance.

Mark

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Yutao Huang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hello @smithmrk ,

Wondering if you have tried the replaceAll API.

For your case, maybe something like this:

let sheet = workbook.getWorksheet("Sheet1"); sheet.replaceAll("*", "", {completeMatch: false, matchCase: false}); sheet.replaceAll("-", "", { completeMatch: false, matchCase: false });

Of if you only want to do that to a single column, you can probably do something like this: sheet.getRange("A1:A20").replaceAll(...).

I did a bit testing to find and replace a single character in a test workbook with 20+ worksheets. It took about 2 seconds to replace all 7k+ occurrences. It worked pretty well when running in both Excel for the web and Power Automate.

Was this reply helpful? Yes No
smithmrk 288 on at

Like (0)

Report
Copy link

Link copied!

@Yutao I apologize, I failed to mention in my original post that it is a Table, so I need to update the entire column not a range of cells in the worksheet but rather a range of cells in a column in a table.

You have any thoughts or ideas on how I can update a column of cells in a table?

On a side note...if there is a way to filter that column and only pull the cells in that column that have a "*" or "-" then I could just loop through those cells only and update just them instead of looping through all the cells in that column.

Thanks,
Mark

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@smithmrk ,

Yes, if you only want to update a column of cells in a table, you can probably do something like this:

let range = workbook.getTable("Table1").getColumn(1).getRangeBetweenHeaderAndTotal() range.replaceAll("*", "", { completeMatch: false, matchCase: false }); range.replaceAll("-", "", { completeMatch: false, matchCase: false });

The API used here is Range.replaceAll.

Please give it a try and let me know what you think!

Was this reply helpful? Yes No
smithmrk 288 on at

Like (0)

Report
Copy link

Link copied!

@Yutao Instead of replacing "*" or "-" with nothing ("")...it removes EVERYTHING in that column!
See screen shot below:

Was this reply helpful? Yes No
Verified answer

Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Oh no!!! I just realized the asterisk "*" is a special character used in Find and replace. It will match any single character. Using wildcard characters in searches (microsoft.com)

To match the actual "*" character, you'll have to add a tilde character "~" in front of it.
let range = workbook.getTable("Table1").getColumn(1).getRangeBetweenHeaderAndTotal() range.replaceAll("~*", "", { completeMatch: false, matchCase: false }); range.replaceAll("-", "", { completeMatch: false, matchCase: false });

Sorry I should have done more testing before posing my answer. Hope you didn't lose any actual data... 😓

Was this reply helpful? Yes No
smithmrk 288 on at

Like (1)

Report
Copy link

Link copied!

BINGO!
That worked...Thanks.

Was this reply helpful? Yes No