Format cell to force a zero

[ipbr21054]

Hi,
My staff enter a numeric value into a cell.
This value needs to start with 0 but some staff keep forgetting and just enter say 12345 as opposed to 012345

Can you advise how this is formatted so next time a value without the 0 is entered then leaving the cell etc will add it.

Thanks

 

[Peter_SSs]

You could use Data Validation to force the users to enter the leading zero.
- Format the cell(s) as Text then
- Apply Data Validation via Data ribbon tab -> Data Validation -> Settings tab -> Allow: Custom -> Formula: =LEFT(A1,1)="0" (where A1 is the address of the active cell) -> (Optional: put a message about the leading zero on the Error Alert tab) -> OK

 

[ipbr21054]

Thanks for the reply.
I was thinking more like a code script etc that when leaving a cell in a particular column then the 0 would be added.

 

[Peter_SSs]

Thanks for the reply.
I was thinking more like a code script etc that when leaving a cell in a particular column then the 0 would be added.

Ok, you can do that. Of course the user will have to have macros enabled. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

I've set this for column D (easy to change in the code) and that column will need to be formatted as Text to retain that leading 0.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("D"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 And IsNumeric(c.Value) And Left(c.Value, 1) <> "0" Then c.Value = 0 & c.Value
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[ipbr21054]

Hi,
Thanks for that but it did not work.
I changed it from D to C but when i type say 12345 and leave the cell it still shows 12345

The sheet is macro enabled.

 

[Peter_SSs]

.. it did not work.
.. when i type say 12345 and leave the cell it still shows 12345

Sounds like perhaps you forgot ..

... that column will need to be formatted as Text to retain that leading 0

 

[ipbr21054]

Hi,
Ive just check and its formatted as Text

 

[ipbr21054]

Hi,
Ive just checked this again & now its working.
Strange.

Thanks very much.

 

[MARK858]

Did you enter a value in the cell or expect an old value to change?

 

[ipbr21054]

I entered a value.
It’s all working now.

Thanks.