Imported CSV file into Excel but cannot convert the numerals from TEXT to a usable number format in order to calculate sums

Question

I've imported a CSv file into excel - but the numbers are in a TEXT which I cannot use in calculations

How can I convert the numerals into a usable format to complete calculations ?

Solution 1

Two possible causes:

a) Decimal point versus decimal comma
   (i.e. numbers are treated as being text, as they do not match "the number format")
   Use Search and replace to rectify it.

b) Cell content stored as Text

• First make sure none of the cells contains a ' as first character, these need to be removed if there are any. (The ' tells Excel to treat the content as being Text, unconditionally)
• Select the cells, then hold CTRL and hit 1, make sure to set "General" or "Number" as format, click OK.
• Then use a free cell, make sure it is not in Text format as above and enter a single 1 in that cell.
• Place the cursor on that cell, hold CTRL and hit C.
• Now select the offending numeric cells again.
• Then use "Paste special" (In the Ribbon for later Excel versions) and select "Multiply", click OK.

The numbers should now act as being numbers when used in formulas.

Solution 2

Load the CSV in a new tab, which you may have done already.

On the sheet that performs the calculations, create a reference to the cells to be calculated but add INT() function to it.

For example:

=INT(MyCSV_data!B2)

This forces the text to become an integer value.

Comments

[Zina]

could you share a row? and the exact procedure you use? it might be the language settings for decimal separator. Also, if you are using the import wizard you can use the preview and set the format of the field to import.

[Hannu]

If you consider the answer below to be suitable, then please click the gray tick beside it.

[Alex M]

is it SOP to perform a fake edit on answers on their 3-year anniversary to push them to the top of the recent list?

[Hannu]

Is it customary to throw ironic comments on text formatting edits?

[Alex M]

I don't know; I'm new to all this. I'm asking you.

[Hannu]

For what it's worth; I "nurture" the answers I have written and try to make them easy to read, improving them in that respect among other respects. At times I see that an answer has grown out of date, making me remove or update it.