I have a set of data called value 2 that has been entered as a character with the units at the end. Some of them are entered as ml/min and some as ul/s, and I'd like to separate the ones that are in ul/s into a new column entirely. What function should I use to select the rows that contain a certain subset of a string (I was going to select the ones that contain "ul/s" to enter them into a new column and then delete them from the value 2 column). I thought the "contain" function would do what I want but it doesn't seem to work so far.
current data table () << Select where (contains (:Value 2, "ul/s"))
This would be a way to do this using the contains() function. Another way to do this would be to use the Cols...Utilities...Text to Columns to separate the Value 2 column into multiple columns.
If you want to script it, you can do this:
dt = data table( "your datatable name" );
dt << new column( "Value 3", Character );
for each row(
dt:"Value 3"n = Char( If( Contains( :"Value 2"n, "ul/s" ) != 0, :Value 2, "" ) );
dt:"Value 2"n = Char( If( Contains( :"Value 3"n, "ul/s" ) != 0, "", :Value 2 ) );
);
Just substitute your actual table name in the first line.
However, if you're trying to deal with mixed units and just want them to be comparable, you could try creating a new column with a formula like this:
Num( Regex( :Value 2, "[0-9]+" ) ) *
If( Contains( :Value 2, "ul/s" ) != 0, 60 / 1000, 1 )
That creates a column where the ul/s data is converted to ml/min by extracting the numerical value (using Regex) then applying a conversion factor depending on the string in the cell. The resulting column is all in ml/min
