How to report EIC signal description as scan m/z value

Hello,

You could split the string on the dash, get the second number, then use a replace function to replace .7 with .0 in the string. The other option you be similar but convert the value to a number, truncate, and then use formatNumber back to 1 decimal value so it will be .0. 

Marty

thank you, Marty! The only problem is that the string won't always end in .7 - if 126.1 is extracted then the string will be 125.8-126.8. Maybe your second suggestion would address this? I am not really sure how to do this in VBA, however...if you have a specific example/solution I would greatly appreciate it!

Hello,

You can try this expression to extract the second mass and convert it to x.0. 

=FormatNumber(Truncate(Val(Choose(1,Split(Choose(2,Split(Signal_Name,"-")),")")))),1)

Thank you for this! I am getting closer to what I'm looking for! I had to tweak your suggestion a bit because I will not always be extracting masses ending in .0 (the extracted mass will frequently end in .1 or .9, for example, I may need 126.0, 245.1, 254.9, etc). With this expression:

=If(Signal_Name like "*EIC*", FormatNumber(Fix(Val(Choose(1,Split(Choose(2,Split(Signal_Name,"-")),")")))*10-7),1)/10,"TIC")

I get the following output:

Which reports the EIC without the extracted range. This is perfect, but I have not been able to get the 126 to report as 126.0. While this isn't critical, I would prefer the decimal if possible. Do you know a way to make this .0 report for this case? I tried a few things unsuccessfully.

Hello,

You turned the value back into a number by dividing by10 outside the formatnumber statement. I would just place the entire statement in a Val() function and then format the column as 1 decimal place. 

Marty

=Val(If(Signal_Name like "*EIC*", FormatNumber(Fix(Val(Choose(1,Split(Choose(2,Split(Signal_Name,"-")),")")))*10-7),1)/10,"TIC"))