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?
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