Problem: Values Not Formatting as Numbers

Option 1: Change the column formatting from Text to General by multiplying the numbers by 1:

1. Enter the number 1 into a cell and copy it.
2. Select the column formatted as text, press Shift+F10 or right-click, and then select Paste Special from the shortcut menu.
3. Select the Multiply option button in the Paste Special dialog box, and then click OK.

Option 2: Change the column formatting from Text to General using the Text to Columns technique:

1. Select the column formatted as text.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select the Fixed width option button.
4. Skip Step 2 of 3.
5. In Step 3 of 3, select the General option button in the Column data format section, and then click Finish.

Screenshot // Problem: Values Not Formatting as Numbers
Problem: Values Not Formatting as Numbers Problem: Values Not Formatting as Numbers Problem: Values Not Formatting as Numbers

Users are saying about us...

  1. Currently, I am writing a small utility program using VB to import some data into Excel. The data I am trying to import consists of date and time joined together, eg. dd/mm/yyyy hh:mm:ss. I was able to import the text data successfully, but Excel always cannot format it into the custom date/time format I want unless I manually format them. Do I need to call out any function in Excel to initiate Excel to recognize the format?

  2. “Hi Alan,

    I am using VB to call out Excel, and in the process importing all my text data into the workbook. 1 of my data is in custom date and time format “”dd/mm/yyyy hh:mm:ss”” , which I pre-formatted the numberformat of the column.
    However, when all my data are in, I find Excel did not format my data to date data. I tried using your tip, it works but that is too tedious as I have hundreds of rows of data. Therefore, I wrote a macro for solution 1, but Excel convert all my data to 01/01/1900 00:00:00, it seems Excel did not take in my text data format well.
    The strange part is if I do it manually, Excel seems to recognize the format of my text data!!”

  3. “Hi Chung,

    I have tried to replicate your issue, but failed.

    However, I suspect it is related to the exact format of the text data that is coming in to Excel, and the interaction with the tex filter.

    You mention that it works manually which re-inforces my suspicion.

    Are you pre-formatting the column where the data will come into, then importing it?

    If so, perhaps try reversing that approach, and import first, then format (from within VBA).

    If that still doesn’t work, but editing and immediately re-entering the data manually (select the cell, press F2, press Enter – Solves problem?), then you could always work around the issue by replicating those actions using VBA – even use SendKeys if you really have to!

    It is not an elegant solution, and really only addresses the symptoms, not the underlying problem, but if it works – do you really care?

    Hope that helps,

    Alan.”

  4. “Hi Alan,

    I can get Excel to read my data by selecting cell, press F2 and press Enter.
    I guess I have to work within VBA then, it just cannot read in the format when I importing them using VB.
    Thanks so much for the help, Alan.

    Chung”

  5. “Hi Chung,

    No problem.

    If you find a better solution, post it back here for posterity and those that follow.

    Thanks,

    Alan.”

    Chung”

  6. “Hi Guys,
    I am facing a prolem in my VBA tool…basiclay I am pulling data from SQL server and populate it in the Excell sheet..this is absolutley working fine in Excell 2000..

    but in Excell 2002 i get this error

    1004The cell or chart you are trying to change is protected and therefore read-only.
    To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted for a password.

    any suggestions from you will be much apreciated..

    thanks
    M.Nainar”

  7. “Hi Nainar,

    I assume you have checked, and the worksheet is unprotected, and can accept the data import?

    You could consider explicitly unprotecting the range or sheet in your VBA code immediately before importing, although I cannot see what you would have to.

    Any other ideas anyone?

    Alan.”

  8. “Hi Nima,

    There are lots of ways of achieving this, but in my experience, the best results are delivered as follows:

    1) Select the entire chart by clicking once on it, somewhere near the edge.

    2) Copy

    3) Go to your PowerPoint presentation page

    4) Select Edit – Paste Special – Picture (Enhanced Meta file)

    Note:

    If you need to change the background to make it transparent, or the font size or anything else, best to do it in Excel, and bring over the ‘finished’ product to PowerPoint – it looks better, and your slides will be more consistent than if you scale each one slightly differently.

    HTH,

    Alan. s”

  9. THANKS!!!!
    I was having major issues figuring out this one. I have a workbook that uses a couple of sheets with data downloaded from our online sites. Key for lookup is a project ID but where the project ID is numeric only, the formula was failing. Tried format cell/column but didn’t work. Convert to Number worked but painful. This fix you posted worked like magic – thx again!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube