How to Parse and Round

Question

Hi Aleryx folks,

I have a field being imported into V_String. It's very weird, because in Excel it has values such as

'IN1.1', 'IN2.2', 'CR1.2', '1.3', '1.4', etc…

Importing it, I get a V_String with some that have long decimals and need rounding…

Field1
CR9.1

CR10.1

CR1.2

CR8.2

CR9.2

CR10.2

3.1

4.1

8.1

9.1

10.1

1.2
2.2000000000000002
3.2

7.2
8.1999999999999993

9.1999999999999993
10.199999999999999
1.1000000000000001
2.1

3.1
4.0999999999999996

5.0999999999999996
6.1

10.1

1.2
2.2000000000000002
3.2

6.2

7.2
8.1999999999999993

9.1999999999999993

10.199999999999999

I've inspected the Excel, it's only storing one digit passed the decimal that I can see.

How can I, where needed, round the decimal to xx.x? So,

2.2000000000000002 = 2.2

10.199999999999999 = 10.2

It's only occuring in cells without letters, fyi.

Thanks!🙏

Matt_Kremsky · Accepted Answer

I solved this by filtering with

REGEX_Match([Field1], "^[A-Za-z].*")

seperating those that begin with a Letter and those that do not. Convert False to Fixed Decimal 255.1, then make it again V_String and combine back together.

clive022lloyd · Accepted Answer

Hello,

Use a Formula tool and convert only the numeric values by checking for digits, then round them to 1 decimal place: IF REGEX_Match([Field1], "^[0-9]") THEN ToString(Round(ToNumber([Field1]),1),1) ELSE [Field1] ENDIF. This forces values like 2.2000000000000002 or 10.199999999999999 to display cleanly as 2.2 and 10.2, while leaving text-prefixed values like CR10.1 unchanged.