I am not sure what is the problem, either is syntax error, or data error.
The data in 1st column F is all text. Some are in millions (with the word 'M' at the end) and some in thousands ((with the word 'K' at the end).
I want to convert all to numbers and put in column 'O'
First, I compare whether the end of the text is either 'M' or 'K' using FIND(), then i used SUBSTITUTE to remove the 'M' or 'K', and multiply by 1,000,000 or 1,000. And i put it in IFS function.
The formulae seems to work for the texts that contain 'M', but not working for 'K'. I am unable to see why. My formulae for the first column as follows:
Please refer to the screenshot: