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:

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

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

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.

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.

Quote from rory

Code

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

Thanks for your suggestion though.

Quote from Carim

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

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

Hi Carim, I wouldn't hold your breath, but if I stumble across a viable solution I'll gladly share it.

Hi Rory,

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

Sub PrepareForImport()


'Deletes guidance Rows 1:2 & Column A...
    Columns(1).EntireColumn.Delete
    Rows("1:2").EntireRow.Delete


'Trims leading & trailing spaces...
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    cell = WorksheetFunction.Trim(cell)
    Next cell
    
'Converts formulas to values...
    ActiveSheet.UsedRange.Copy
    ActiveSheet.UsedRange.PasteSpecial xlPasteValues


'Converts number to text...
    ActiveSheet.UsedRange.NumberFormat = "@"
    
'Removes validation...
    ActiveSheet.UsedRange.Validation.Delete


'Removes lookup worksheets...
    Dim ws As Worksheet
    
    Application.DisplayAlerts = False
    
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> ActiveWorkbook.ActiveSheet.Name Then ws.Delete
        Next
    
    Application.DisplayAlerts = True


End Sub

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.

Quote from rory

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!

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