Slightly off-topic - need help with Regular Expressions


#1

I apologise for this non-Panorama query but I need help quickly to help out a friend in need.

I have a block of text which may or may not contain a number in the form #.###… where the number of decimals is unknown but is greater than two. I want to locate all such occurrences and truncate them to two decimal places. Ideally, I’d like to round them to two places but I suspect that RegEx is not up to that.

Can somebody help please.


#2

Perhaps I should clarify this a tad - it is to some extent a Panorama X problem in that the text block is the contents of a data cell.


#3

This formula worked on the contents of the active field.

regexreplace(«»,"(\.\d\d)\d+","$1")

It finds every instance of a decimal point followed by 3 or more digits, keeps the decimal point and the first two of the digits, and throws the extra digits away.

Replace the «» with the name of the field, if it’s not the active field.


#4

This formula will round.

«»[1,"."]+ArrayFilter(«»[".",-1][2,-1],".",|||?(import() regexmatch "\A\d{3,}.*",pattern(val("0."+import()),"#.##")[3,-1]+import()["≠0-9",-1],import())|||)

#5

This is another variation using replacemultiple(, regexarray( and arrayfilter( to truncate and round the numbers within the text.

replacemultiple(«», regexarray(«»,"(\d*?\.\d\d)\d+",","),
    arrayfilter(regexarray(«»,"(\d*?\.\d\d)\d+",","), ",",
    {pattern(round(val(import()),.01),"#.##")["≠0",-1]}), ",")