Custom number format

Question

Lectori salutem ;-),

Just now I experienced something (not so) funny. Though ... not very important, just puzzling.

I use Excel 2010 (Dutch).

When I create a simple custom number format 000 that cell changes to Special>Chinese (Taiwan). 

And 0000 switches to Portuguese (Standard).

How come?

And ... how to prevent this behavior.

Answer 1

Yes, I'm Dutch (I live in Leiden).

You could run the following macro. It deletes all unused custom number formats, i.e. formats not used in any cells in worksheets in the active workbook. It does not check number formats used in charts, so custom formats used only in charts will be removed.

Sub RemoveUnusedNumberFormats()

  Dim strOldFormat As String

  Dim strNewFormat As String

  Dim aCell As Range

  Dim sht As Worksheet

  Dim strFormats() As String

  Dim fFormatsUsed() As Boolean

  Dim i As Integer

  If ActiveWorkbook.Worksheets.Count = 0 Then

    MsgBox "The active workbook doesn't contain any worksheets.", vbInformation

    Exit Sub

  End If

  On Error GoTo Exit_Sub

  Application.Cursor = xlWait

  ReDim strFormats(1000)

  ReDim fFormatsUsed(1000)

  Set aCell = Range("A1")

  aCell.Select

  strOldFormat = aCell.NumberFormatLocal

  aCell.NumberFormat = "General"

  strFormats(0) = "General"

  strNewFormat = aCell.NumberFormatLocal

  i = 1

  Do

    ' Dialog requires local format

    SendKeys "{TAB 3}{DOWN}{ENTER}"

    Application.Dialogs(xlDialogFormatNumber).Show strNewFormat

    strFormats(i) = aCell.NumberFormat

    strNewFormat = aCell.NumberFormatLocal

    i = i + 1

  Loop Until strFormats(i - 1) = strFormats(i - 2)

  aCell.NumberFormatLocal = strOldFormat

  ReDim Preserve strFormats(i - 2)

  ReDim Preserve fFormatsUsed(i - 2)

  For Each sht In ActiveWorkbook.Worksheets

    For Each aCell In sht.UsedRange

      For i = 0 To UBound(strFormats)

        If aCell.NumberFormat = strFormats(i) Then

          fFormatsUsed(i) = True

          Exit For

        End If

      Next i

    Next aCell

  Next sht

  ' Suppress errors for built-in formats

  On Error Resume Next

  For i = 0 To UBound(strFormats)

    If Not fFormatsUsed(i) Then

      ' DeleteNumberFormat requires international format

      ActiveWorkbook.DeleteNumberFormat strFormats(i)

    End If

  Next i

Exit_Sub:

  Set aCell = Nothing

  Set sht = Nothing

  Erase strFormats

  Erase fFormatsUsed

  Application.Cursor = xlDefault

End Sub

Answer 2

Excel sometimes is too clever for its own good. It defines "special" number formats for many locales. For example, the number format

####-##-###

is specific to Dutch (The Netherlands): it's the format of the Dutch social security number (Burger Service Nummer, voorheen SoFi-nummer).

Now, if someone anywhere in the world, for example in Taiwan, defines a custom number format ####-##-###, Excel recognizes it as the format of the Dutch social security number, and will display it as such in the Number tab of the Format Cells dialog. There is nothing you can do to prevent that, and it doesn't do any harm. When you select the Custom category, you'll see the format ####-##-### again.

The same goes for custom formats such as 000, 0000 and 00000, that coincide with a "special" format for Taiwan, Portugal and the United States, respectively.

Answer 3

A workbook might contain only chart sheets, and no worksheets.

You could add a line

Application.DisplayAlerts = False

near the beginning of the macro, and

Application.DisplayAlerts = True

near the end.

Answer 4

Hi Hans,

Great, the macro works! Thank you. The code looks smart, as far as I understand it.

A few tiny point:

• can a workbook have zero sheets?
• I prefer to locate (and return to) user's active cell before selecting A1
• Application.Screenupdating = False doesn't suppress the dialog (a pitty)

and I added a small MsgBox to warn about possible deletion of custom formats in charts.

I'll visit and examine your site. Must be interesting.

Answer 5

Hi Hans,

Pfff ... Glad that I didn't mess up my Excel ;-).

One (simple) next question (this is not a proper  way): 

• How to get rid of tens of no-longer-used custom number formats in a workbook? 

PS: Hans ... kaaskop zoals ik?