How to format a number

[YNWA]

Hi,

I need a cell to format or input mask after input. The number is an electricity meter reading.

The number always starts with 00, so this needs to be at the front but ideally the user shouldn't have to type the 00 part.

So the format I need is 00 XXX XXX XX XXXX XXXX XXX.

Thats 00 followed by 3, 3, 2, 4, 4, 3 digits. So 19 numbers, plus the 00 at the front = 21 numbers.

Any ideas?

I just cant get mine to work, trying "00"#,###,###,##,####,####,###

[YNWA]

In fact I done "00" ### ### ## #### #### ### and I seem to have it.

[Russell Dawson]

Please don't forget to mark your thread as solved.

[shg]

Notice that the last four digits are always 0?

[YNWA]

Notice that the last four digits are always 0?

So it is, missed that. Can you help?

Thanks

[shg]

Excel doesn't store numbers with more than 15-digit precision. Your meter-reading results need to be text.

[YNWA]

Ah ok. Is it possible to have the outcome formatting to show as XX XXX XXX XX XXXX XXXX XXX? No need to store 0's etc..., just re-jig the view once number entered?

[shg]

I reckon.

How are you planning to subtract readings to get usage?

[YNWA]

Sorry its not a meter reading its an account number. for display purposes only

[shg]

Code goes in the appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target(1)
        If .Column = 1 And VarType(.Value) = vbString Then
            On Error GoTo Oops
            Application.EnableEvents = False
            .Value = Format(Replace(.Value, " ", ""), """00""@@ @@@ @@@ @@ @@@@ @@@@ @@@")
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

[YNWA]

Code goes in the appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target(1)
        If .Column = 1 And VarType(.Value) = vbString Then
            On Error GoTo Oops
            Application.EnableEvents = False
            .Value = Format(Replace(.Value, " ", ""), """00""@@ @@@ @@@ @@ @@@@ @@@@ @@@")
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

Thanks but how does it know which cell to format?

[shg]

It formats entries you make in col A.

[YNWA]

It formats entries you make in col A.

How could I make it work for sale a merged column say B20 through F20.?

[shg]

Post a workbook after you've unmerged the cells.

[YNWA]

Post a workbook after you've unmerged the cells.

Sorry I dont follow?

[shg]

1. Unmerge the cells you have merged.

2. Post the workbook and explain where you enter the meter numbers.

I don't help people work with merged cells. I took an oath.

[YNWA]

Sorry I can't post the sheets as they contain personal data.

I will unmerge the cell, so go off cell E20?