Convert "1.0M-2.0M" to "1,500,000" in Excel-CodePudding

I have a large column of numbers in the format x.xM - x.xM or xK - xM . (M=millions, K= thousands ) They aren't numeric.

I need to convert these to the numeric mean of the range.

Is there a quick way to do this rather than manually calculating each cell's average and replacing it?

Thanks

CodePudding user response：

IN Office 365 we can use LET to shorten the formula

=LET(
    r,A1,
    lt,LEFT(r,FIND("-",r)-1),
    rt,MID(r,FIND("-",r) 1,LEN(r)),
    AVERAGE(LEFT(lt,LEN(lt)-1)*10^(MATCH(RIGHT(lt),{"K","M"},0)*3),
        LEFT(rt,LEN(rt)-1)*10^(MATCH(RIGHT(rt),{"K","M"},0)*3)))

Without Let:

=AVERAGE(LEFT(LEFT(A1,FIND("-",A1)-1),LEN(LEFT(A1,FIND("-",A1)-1))-1)*10^(MATCH(RIGHT(LEFT(A1,FIND("-",A1)-1)),{"K","M"},0)*3),LEFT(MID(A1,FIND("-",A1) 1,LEN(A1)),LEN(MID(A1,FIND("-",A1) 1,LEN(A1)))-1)*10^(MATCH(RIGHT(MID(A1,FIND("-",A1) 1,LEN(A1))),{"K","M"},0)*3))

CodePudding user response：

So, quick and dirty but you can improve:

=((LEFT(A1,FIND("-",A1,1)-2)*1) (LEFT(RIGHT(A1,LEN(A1)-FIND("-",A1,1)),2)*1))/2

Just to prove that I tested it and it works: