How to filter out non-numbers by evaluating full string?

Question

I have a column of percentages that can show up in any of the following formats:

≈ +8.5%
Rate Neutral
≈ (16.2%)

+2.0% and +3.5%

0.15
1.0999999999999999E-2
-3.0000000000000001E-3
-0.104

Using the ToNumber functions allows me to catch the following values from above as not being numbers, which I then handle separately

≈ +8.5%
Rate Neutral
≈ (16.2%)

But any values that start with a number but is not completely a number gets converted whereas I'd like to also catch them so they are not just assumed to be 2 (in the case of +2.0% and +3.5%)

I have tried checking for any alphabetic characters but that also pulls aside scientific numbers which I don't want.

Is there any function or combination of functions that allows me to try and convert the entire string to a number, and if it doesn't work, it doesn't pass?

lynnesonney · Answer

Take a look at this as an option. I've used a regex function to allow only digits, negative signs (dash), decimals, and the letter E.

Let me know if this works!

1_5.30.23.yxmd

tristank · Answer

Hi @pateludit87

Would you put in some sample data and list the cases where you want to handle the values separately from the rest of the data? I'd like to mess around with some different approaches.

Thanks,

Tristan