Custom Number Format With Dash (-) Mark

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all...

how to make custom number format for like this:
dataafter formula
MHFJB8EM6G1011862MHF-JB8-EM6-G-1011862
MHFE2J2JCK033589MHF-E2J-2JC-K-033589
criteria : 3 digits from left in twice then digits 10th
any help, thank in advance

.sst
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Copy the code to the spreadsheet module.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ms As String, nms As String
    
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    ms = Target.Value
    If Len(ms) < 10 Then Exit Sub
    Application.EnableEvents = False
    nms = Left(ms, 3) & "-" & Mid(ms, 4, 3) & "-" & Mid(ms, 7, 3) & "-" & Mid(ms, 10, 1) & IIf(Len(ms) > 10, "-" & Mid(ms, 11, 255), "")
    Target.Offset(0, 1) = nms
    Target.Offset(0, 1).Characters(13, 1).Font.Bold = True
    Application.EnableEvents = True
End Sub
 
Upvote 0
Copy the code to the spreadsheet module.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ms As String, nms As String
   
    If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
    ms = Target.Value
    If Len(ms) < 10 Then Exit Sub
    Application.EnableEvents = False
    nms = Left(ms, 3) & "-" & Mid(ms, 4, 3) & "-" & Mid(ms, 7, 3) & "-" & Mid(ms, 10, 1) & IIf(Len(ms) > 10, "-" & Mid(ms, 11, 255), "")
    Target.Offset(0, 1) = nms
    Target.Offset(0, 1).Characters(13, 1).Font.Bold = True
    Application.EnableEvents = True
End Sub

hi Maras....thank for your help..but for another way can use not vba
 
Upvote 0
how to make custom number format for like this:

criteria : 3 digits from left in twice then digits 10th
First a couple of terminology points.
Custom number formats are for numbers & you have text, not numbers
You refer to 3 digits at the left and the 10th digit but digits are also numbers and the characters you are referring to are not numbers.

You could try this in another column

20 07 08.xlsm
AB
1MHFJB8EM6G1011862MHF-JB8-EM6-G-1011862
2MHFE2J2JCK033589MHF-E2J-2JC-K-033589
Insert -
Cell Formulas
RangeFormula
B1:B2B1=REPLACE(REPLACE(REPLACE(REPLACE(A1,11,0,"-"),10,0,"-"),7,0,"-"),4,0,"-")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top