Need to programmatically set Excel cell format to text

Posted on 2011-10-13
Last Modified: 2012-05-12
I am writing data into Excel from and need to set some cell formats to text, to ensure that leading zeroes are not lost. So far, I have tried adding a leading apostrophe to the string I write into the cell, setting the cell's numberformat to "Text" and setting it to "000000". None of these work.

There must be a magic solution??
Question by:christinaemmm
    LVL 5

    Accepted Solution

    Both of these should work:

        ' uses apostrophe
        ActiveCell.FormulaR1C1 = "'000000"

        ' uses text formatting
        Selection.NumberFormat = "@"
        ActiveCell.FormulaR1C1 = "000000"

    Hope this helps.
    LVL 29

    Expert Comment

    what do you mean by You tried an appostroph and 00000 formating and it didn't work what number you have at first and what did you get can you post some examples as what you indicate should work with a slight change

    Author Comment

    I suddenly wondered whether it was because I save the ss as a csv file; when I tested, I found that the control settings for some of the values were not being passed correctly. So mea culpa, having fixed my code, it does work, including saving the ss as a csv file.

    slycoder, I didn't know about using @ - is there any practical difference between the options of this or inserting an apostrophe? I have now tried both and they both seem fine, but it would be good to know if there are any little 'gotcha's with either solution.

    Author Closing Comment

    Thanks, slycoder!
    LVL 5

    Expert Comment

    There are no 'gotchas' that I know of with using either method.

    the apostrophe is a "literal" mark and has been used since the days of VisiCalc

    the @ formatting is what I got when I recorded a macro and changed the formatting of the cell to Text.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now