1

I have data in csv where the Milions, thousands, are specified by "M", resp "K" characters.

Example:

abc;1.4M
def;1.45M
ghi;850K

I want to import them, now how can I force Excel to treat the values in second column as numbers (I want to draw a Sparkline based on those) ?

I've tried to apply custom format on second column and then import the data without success.

I can manage to have input data either with "M" or "K" (i.e. column 2 will not have "M" and "K" mixed. 3rd row would be then: ghi;0.85M), so the solution which would treat millions and thousands separately will be also very helpful.

Knox
  • 2,909
  • 11
  • 37
  • 65
RobertG
  • 63
  • 4

2 Answers2

1

If this is a one time thing, it may be easier to import it as two columns of text. Then select the column with the numbers and letters in it, and do a Find And Replace. Replace M with E06. Do a replace all. Replace all K with E03.

This takes advantage of scientific notation where the E tells Excel to multiple the number by 10 to the power of the digits following the E. So E03 is really multiplying the number by 1000.

The numbers will show up in scientific notation, but it's easy to reformat the column to accounting or whatever format you like.

Knox
  • 2,909
  • 11
  • 37
  • 65
1

I would issue an SQL statement to the CSV file (see here and here):

SELECT Field1, CDbl(Field2) * IIF(
        RightChar="M", 
        1024*1024, 
        IIF(
            RightChar="K", 
            1024, 
            1
        )
    ) AS FinalValue
FROM (
    SELECT Field1, Field2, RIGHT(Field2, 1) AS RightChar
    FROM [Sheet1$]
) AS t1

and you can use Excel's CopyFromRecordset to paste the results into a new Excel worksheet.

Community
  • 1
  • 1
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Thanks Zev, I didn't know that it's actually possible to treat workbook as DB using ActiveX Data objects. You've opened new horizons to me! However I will use Knox's proposal - right now it's sufficient for me. – RobertG Nov 13 '16 at 18:05