Office Script to apply "Sort number and numbers stored as text separately"

[duger99]

Hi friends - I have a situation where a sort field has been converted to text and is a mix of numbers and text. The values include numbers like "001", "002", etc. and values like "0.1, 066". When sorting this column in excel, it brings up a Sort Warning dialogue box and gives the option to "Sort numbers and numbers stored as text separately".

I would like to do this via an office script; however, I have been unsuccessful in finding a method to do so. Does it exist?

Here is the code I am using, which is part of a broader script:

// Sort the range range A22:BW226 on selectedSheet
  portfolio_reporting.getRange("A22:BW226").getSort().apply([{ key: 1, ascending: true}], false, true, ExcelScript.SortOrientation.rows);

Thank you for your assistance!

 

[ExcelToDAX]

How about

portfolio_reporting.getRange("A22:BW226").getSort().apply([{ key: 1, ascending: true, dataOption: ExcelScript.SortDataOption.textAsNumber }], false, true, ExcelScript.SortOrientation.rows)

Here is the documentation: ExcelScript.SortField interface - Office Scripts

 

[duger99]

@ExcelToDAX - That options ends of sorting it where the numeric values (001, 002) are above "0.1, 066". The option "Sort numbers and numbers stored as text separately" puts the "0.1, 066" above "001".

 

[ExcelToDAX]

That is what you were asking for, to have an Office Scripts code that sorts the numbers and numbers stored as text separately. What are you trying to accomplish? What should the final output look like?