Aug 03 2022 12:48 PM
have this number format:
12345-678-91
need to tell Excel to add a 0 in front of the number 6 to make if a 4-digit number....how do i do that in excel?
Aug 03 2022 01:03 PM
If the cell contains a number and if it is just for display purposes, you could create and apply the custom number format
00000"-0"000"-"00
Otherwise, you could create the following macro. Select the cells to be processed before running it.
Sub Add0()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Selection
If rng.Text Like "[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]" Then
rng.Value = Left(rng.Text, 6) & "0" & Right(rng.Text, 6)
End If
Next rng
Application.ScreenUpdating = True
End Sub
Aug 03 2022 01:49 PM
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy and paste the code into the module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Select the cells.
Press Alt+F8 to display the Macros dialog.
Select the macro and click Run.