Apply NumberFormat = "@" (without entering cells or using Text to Columns function)

  • Hi,


    I have a very basic macro to convert all integers to stings within a used range:


    Code
    Sub ConvertNumberToText()
    
    
        ActiveSheet.UsedRange.NumberFormat = "@"
    
    
    End Sub


    This would work fine, BUT Excel doesn't recognize a NumberFormat change until you enter & exit each cell (only then do you see the cell warning "Number Stored as Text"). I know people use the Text To Columns function to workaround this 'quirk' but I really need a programmatic solution as I'm cleansing large data sets and integers can appear at random.


    Any help would be very welcome. Thank you, in hopeful anticipation :)

  • Hello,


    You could test the following macro:


    Code
    Sub NumbersToText()
        Cells.SpecialCells(xlCellTypeConstants, 1).NumberFormat = "@"
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Thanks for taking the time to reply but unfortunately this yields the same result.


    Although the format's changed, it's not recognized in the sheet until you enter and exit a cell. Been driving me crazy!


    Thanks again,
    Jim

  • Jim,


    Sorry ... but it seems to me what you are after is not possible ... since Excel will only recognize your number ... after you have entered it in a cell ...


    Indeed while typing ... you can always use the backspace key to change your mind ... and eventually type a text ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I feared that would be the case :(


    I'm cleansing large data sets prior to importing to a PLM system. To ensure that data lands accurately I need to scan for numbers and convert them to text (among other checks & corrections) - I really don't want to edit in-sheet at all.


    Could I append then remove a character so the cell updates? If not I guess I need to think of another plan.


    Thans again.

  • You could use:


    Code
    With activesheet.usedrange
        .Numberformat = "@"
        .Value2 = .Value2
    End with


    as long as you don't have formulas to worry about.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Thanks Rory,


    Formulas wouldn't be a problem as they're removed earlier in the code.


    Unfortunately though, this code yields the same result, the change still isn't recognized in the sheet unless you enter & exit a cell.


    Code
    With activesheet.usedrange
    .Numberformat = "@"
    .Value2 = .Value2
    End with


    Thanks for your suggestion though.

  • Hello Jim,


    Should you eventually come up with a viable solution ...


    It would be very nice of you to share it with the Forum ...:smile:


    Take Care

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The code I posted works fine for me (I've used it many times). Can you post a workbook showing it not working?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi Rory,


    I attached my test workbook and paste the full subroutine below.



    As you can see, 'Number to Text' is the 4th of 6 data cleansing steps I need to execute prior to importing to a [rather sensitive] PLM system.


    If I view cell properties after running the subroutine it does say "Text" but the sheet only truly recognizes it as such once you enter & exit a cell (only then do you see the "Number Stored as Text" warning triangle). It's the same no matter what code I run.


    It seems to be a known 'quirk' that most people work around using the Text to Columns function - https://www.groovypost.com/howto/ref...matting-excel/ - but numbers could appear anywhere in the large data sets I'm working with so that's no good to me.


    I should also point out that I'm running code from my personal workbook (xlsb) and my data is held in a table... but I've tried converting to range before running the code and it still doesn't work!


    Any help would be gratefully received.

  • You haven't implemented what I suggested in there, so I'm not surprised it doesn't work! ;)


    Although I have just noticed that it doesn't work in your file as you said! Apologies. Use .Formula = .Formula instead of .Value2 = .Value2 and it should fix it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • You haven't implemented what I suggested in there, so I'm not surprised it doesn't work! ;).


    Hi Rory,


    I did test .Value2 = .Value2 in isolation though :)


    SUCCESS!


    .Formula = .Formula works a treat. Thank you so much!

    Code
    Sub NumberToText()
    
    
        With ActiveSheet.UsedRange
        .NumberFormat = "@"
        .Formula = .Formula
        End With
    
    
    End Sub


    I'm still a noob (1 week since 1st bit of code) but I hope to help others in the future. Jim

  • You're welcome.


    I'm actually quite intrigued as to why my usual code didn't work, so your sample will be useful to me too! :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!