Regex convert text to numbers

[#1]

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

[#2]

---

this could be done in a much simpler way

---

What do you consider to be this much simpler?

[#3]

Jarmo

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

This?

Slán

SpannerHead

[#4]

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

[#5 · Accepted solution]

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)

[#6]

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