I have a column with 3 types of input values in 1 column as shown in the below table (ex. 160 BP, 0.5%, 101.0 BP) and I have to perform cleaning in a way to get values as shown in output column
Input | Output |
160 BP | 160 |
0.5 % | 50 |
0.5 BP | 0.5 |
105 BP | 105 |
101.0 BP | 101 |
100.0 BP | 100 |
1.88 BP | 1.88 |
1.89 % | 189 |
1.89 BP | 1.89 |
103 BP | 103 |
160.0 BP | 160 |
1 % | 100 |
0.95 % | 95 |
1.91 % | 191 |
150.0 BP | 150 |
Solved! Go to Solution.
Hey @PB41091, as far as I can tell, these look to be the conditions needed to get your expected values. However, please do let us know if not or if you face any issues when applying this to a wider dataset!
IF Contains([Input], 'BP') then ToNumber(Replace([Input], ' BP', '')) else ToNumber(Replace([Input], ' %', ''))*100 endif
Perfect, it is working. Thanks for your help :)