nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Regex convert text to numbers

SpannerHead

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

Accepted answers

txnelson

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);<BR /><BR />// Change column to numeric
dt:val << data type(numeric)<<modeling type(continuous)

All comments

jthi

this could be done in a much simpler way

What do you consider to be this much simpler?

SpannerHead

Jarmo

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

This?

Slán

SpannerHead

jthi

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).

txnelson

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);<BR /><BR />// Change column to numeric
dt:val << data type(numeric)<<modeling type(continuous)

SpannerHead

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