string formating issue in calc/excel

Posted on 2011-05-07
Last Modified: 2012-05-11
I have a csv file. that contains the following field "1:1:2.05". If i view it in notepad is


but if i open in excel or calc it get transposed to the following.

How can i keep it in the same format.
Question by:frosty1
    LVL 50

    Accepted Solution


    you never list what "the following" is. I assume the value gets interpreted as a time value and stored as a decimal number. In Excel, you could try formatting the resulting cells with custom format


    and it will display as 1:1:2.05

    cheers, teylyn

    LVL 3

    Expert Comment

    Check this:

    You can open the Format dialog just by right-clicking the cell(s) and choosing the menu option...
    If the string is gets really changed when you open the file via Excel changing the format to Text will help...
    LVL 22

    Expert Comment

    You could also prefix with with an apostrophe, which tells Excel to keep it as a text string.
    The biggest problem is that you'll have to do that manually before importing.

    Another option is to change the file extension to text, then when the wizard appears, select that column and choose Text as the data type (rather than General.) (As Morphor indicated)
    LVL 4

    Expert Comment

    teylyn's is the only solution i can think of.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now