1

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:

`=IFS(FIND("M",F2)>0,SUBSTITUTE(F2,"M","")*1000000,FIND("K",F2)>0,SUBSTITUTE(F2,"K","")*1000)`

Please refer to the screenshot:

screenshot

1
  • Hi @sam , your formula returns #Value error, because every time it encounters with first logical test,, and fails to read the second logical test,,, this can be handled with trapping an ERROR,, like this with IF,,,, =IFERROR(IF(FIND("M",A1),SUBSTITUTE(A1,"M","")*1000000),IF(FIND("K",A1),SUBSTITUTE(A1,"K","")*1000,0)),,, here IF(FIND("K",A1),,, is, Test if Error ☺ – Rajesh S Jun 2 '20 at 6:55
2

The formula is not able to move past the error if M is not found, you need to catch the error, if they are the only two values (K and M) that are going to show use something like:

=IFS(ISERROR(FIND("M",F2)),SUBSTITUTE(F2,"K","")*1000,ISERROR(FIND("K",F2)),SUBSTITUTE(F2,"M","")*1000000)

otherwise next another if function to return a value if M or K not present.

1
  • Thanks a lot Justin! – sam Jun 2 '20 at 2:52
0

You have to specify that the cell must shown the unchanged value if K and M are not present.

I prefer IF over IFS. Always nasty problems popping up with IFS. Try

=IF(ISNUMBER(FIND("K",F2)),SUBSTITUTE(F2,"K","")*1000,IF (ISNUMBER(FIND("M",F2)),SUBSTITUTE(F2,"M","")*1000000, F2))
2
  • Thanks a lot ruud! – sam Jun 2 '20 at 2:53
  • I have tried replacing "ISNUMBER" with "ISERROR", seems to be returning an error. The formulae is this. Why is it so? =IF(ISERROR(FIND("K",F2)),SUBSTITUTE(F2,"K","")*1000,IF (ISERROR(FIND("M",F2)),SUBSTITUTE(F2,"M","")*1000000, F2)) – sam Jun 2 '20 at 3:59
0

If you have a later version of Excel that supports dynamic arrays, you can use a slightly different and (in my opinion) simpler to manage approach:

enter image description here

I've included additional columns to show the progress towards the green solution.

First, get the scale indicator (K or M or whatever). Then use a fixed array inside XLOOKUP to translate that into a multiplier. Then, we just need to get the number in the original cell and multiply it by the multiplier.

This is the formula in the green cell E3:

=NUMBERVALUE(LEFT($A3:$A6,LEN($A3:$A6)-1))*XLOOKUP(RIGHT($A3:$A6,1),{"M","K"},{1000000,1000})
1
  • Thanks a lot Owen! – sam Jun 2 '20 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.