topic Regex convert text to numbers in Discussions

Regex convert text to numbers

SpannerHead — Fri, 16 Aug 2024 18:51:50 GMT

I have a property listed in a column (Device in this case) that contains a string. Somewhere inside the string is a reference to a numeric value that I need to extract and convert to an actual number.

The formula below works but it seems like this could be done in a much simpler way.

Regex( Substr( :Device, 6 ), "K", "000", GLOBALREPLACE )

Any instance of 1K is replaced by 1000 in this case.

Slán

SpannerHead

Re: Regex convert text to numbers

jthi — Fri, 16 Aug 2024 19:05:43 GMT

this could be done in a much simpler way

What do you consider to be this much simpler?

Re: Regex convert text to numbers

SpannerHead — Fri, 16 Aug 2024 19:15:27 GMT

Jarmo

Num( Regex( :Device, "(\d+)K", "\1" ) ) * 1000

This?

Slán

SpannerHead

Re: Regex convert text to numbers

jthi — Fri, 16 Aug 2024 19:24:34 GMT

So... use that...? These are highly specific to your data and application (for example usually "k" would be kilo not "K")

If you have to fix numbers written as strings into numbers, I would say there is never a single simple solution for that (good example could be excel, it just assumes something and totally ruins your data in irreversible way).

Re: Regex convert text to numbers

txnelson — Fri, 16 Aug 2024 19:37:01 GMT

Here is a simple Example using JSL

names default to here(1);
dt = New Table( "example",
	Add Rows( 2 ),
	New Column( "val", Character, "Nominal", Set Values( {"1K", "5K"} ) )
);
wait(5);

// Convert the values
for each row(
	:val = Substitute(:val,"K","000")
);
wait(0);

// Change column to numeric
dt:val << data type(numeric)<<modeling type(continuous)

Re: Regex convert text to numbers

SpannerHead — Fri, 16 Aug 2024 20:16:44 GMT

Jarmo

All of these things work and you're right, my problem is very specific, that can happen to people sometimes. The solution I referenced gets a spanner in the works if there is more than one letter K (defaults to capitals in my standard data output) in my data.

Thanks anyway

Slán

SpannerHead