782294_en-US

liliconcom

Regex convert text to numbers

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

Automation and ScriptingRe: Regex convert text to numbers

---

this could be done in a much simpler way

---

What do you consider to be this much simpler?

Re: Regex convert text to numbers

Jarmo

 

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

This?

 

Slán

 

SpannerHead

Re: Regex convert text to numbers

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

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=""></MODELING>

Re: Regex convert text to numbers

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