How to apply custom text format with Excel Scripts

Question

I am trying to set up a custom format using Office scripts, so that when the number 10 is entered in a cell, it displays the string ten instead. Of course, the method will work for any number/string pair.

This can be done manually and with VBA, but I need Typescript for this one. I got the syntax from the action recorder, but when running the snippet below an error is thrown: The argument is invalid, missing or has an incorrect format.

I would accept a Script Lab solution as well.

function main(workbook: ExcelScript.Workbook) {
let s = workbook.getActiveWorksheet().getRange("g9")
s.setValue("7")
s.setNumberFormatLocal("[=7]\"\"seven\"\";General"); }

Is this Office-JS or Office Scripts? They're not the same thing. I suspect you're meaning Office Scripts. Also, is this a brand new requirement or are you trying to convert from an existing VBA solution because what you're asking for isn't just going to work via simple Excel formatting. How do you propose a number is converted to words? — Skin
– Skin, Commented Oct 3, 2024 at 0:26
Hello @Skin. It is Office scripts, from the automate tab. I will try to correct the tag. It is a new requirement, I mentioned VBA to show custom formatting can be done programmatically. There will a conversion table from numbers to words. The issue is more basic, the syntax to convert a single pair is not working. Thanks for your time — Seldon
– Seldon, Commented Oct 3, 2024 at 1:25
Well explained, I underdtand now. Glad you've zeroed in on the basic issue, well done. — Skin
– Skin, Commented Oct 3, 2024 at 1:40
Narrow it down with logging statements to tell us which line, I suspect its the let s =. This will point your troubleshooting at ref libraries and excel versions and early/late binding. I doubt its the assignment of cells but I dont know they maybe locked or have conflicting existing logic. — Jeremy Thompson
– Jeremy Thompson, Commented Oct 3, 2024 at 1:51

taller · Accepted Answer · 2024-10-03 23:58:25Z

1

Similar to VBA macro recording, the action recorder is not always entirely right. Duplicated double quotation marks ("") is used in VBA, but it doesn't work with OfficeScripts.

Try

    s.selectedSheet.getRange("G9").setNumberFormatLocal('[=7]"seven";General');

edited Oct 3, 2024 at 23:58

answered Oct 3, 2024 at 2:22

taller

19.7k2 gold badges9 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

How to apply custom text format with Excel Scripts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related