Text data cleaning

[PB41091]

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

[DataNath · Accepted 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

[PB41091]

Perfect, it is working. Thanks for your help :)