Combination Custom Number Format with both Numbers and Capital Letters

Wow! Thank you for all the work on that! Unfortunately, I don't think this is what I need as I don't need to randomly generate the numbers, I am just entering the IDs. I am hoping there is a custom number format that I could create so Excel would put in the dashes in the proper places and change the letters to capitals yet allow numbers as well. (Kind of like how in the more number formats there is the special category that formats phone numbers and social security numbers).

Please expand on what it is you are trying to do.

One person has guessed you are trying to generate random test data.

My guess is that you are trying to setup an input validation "mask".

You can download this free ebook.  Tip #16 will get you part of the way there:

@ 101 Excel 2013 Tips, Tricks and Timesavers-Wiley.pdf  314pg (2013) (downloaded) John Walkenbach-

https://drive.google.com/file/d/0B0DBjBjJsSoLX1Q4VWFlM0xESm8/view?usp=sharing 

Part II: FormattingTip 16: Creating Custom Number Formats           pg 54

There is nothing in Excel that can give you exactly the format control you want, all in one cell.

You would be best off if you split the input into the separate cells in the example. Then you can build conditional checks and formatting to handle the limitations.

Hi hawaiiham2000

Hope you are ok

You reached out to the community looking for help And we responded to you.

Sadly, we have nothing but silence from you.

And I just wonder if you have checked or tried the solution above?

Did you find a solution to your question elsewhere?

Do you require any further help with this?

Let us know

Hi hawaiiham2000

If you are trying to generate random IDs or MBIs that will follow the pattern in your post

Please try this UDF custom function in VBA, created to meet your requirements

This is the code

****************************************************************************

Public Function RandomMBIs()

Dim P1, P2, P4, P5, P7, P8, P9, P10, P11 As Integer

Dim P3, P6 As Variant

P1 = Application.WorksheetFunction.RandBetween(1, 9)  ''Generates Type C

P2 = Chr(Application.WorksheetFunction.RandBetween(65, 90))  ''Generates Type A

P3 = Application.WorksheetFunction.RandBetween(0, 1)  ''Switch to Generate Type A or N

P4 = Application.WorksheetFunction.RandBetween(0, 9)  ''Generates Type N

P5 = Chr(Application.WorksheetFunction.RandBetween(65, 90))  ''Generates Type A

P6 = Application.WorksheetFunction.RandBetween(0, 1)  ''Switch to Generate Type A or N

P7 = Application.WorksheetFunction.RandBetween(0, 9)  ''Generates Type N

P8 = Chr(Application.WorksheetFunction.RandBetween(65, 90))  ''Generates Type A

P9 = Chr(Application.WorksheetFunction.RandBetween(65, 90))  ''Generates Type A

P10 = Application.WorksheetFunction.RandBetween(0, 9)  ''Generates Type N

P11 = Application.WorksheetFunction.RandBetween(0, 9)  ''Generates Type N

''***************************************************************

''Switch to Generates Type A or N randomly for Position 3

If P3 = 0 Then

        P3 = Application.WorksheetFunction.RandBetween(0, 9)

Else

        P3 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

ReEvP3:

''To Exclude characters (S, L, O, I, B and Z) for Position 3

If P3 = "S" Or P3 = "L" Or P3 = "O" Or P3 = "I" Or P3 = "B" Or P3 = "Z" Then

        P3 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP3

End If

End If

''*****************************************************************

''Switch to Generates Type A or N randomly for Position 6

If P6 = 0 Then

P6 = Application.WorksheetFunction.RandBetween(0, 9)

Else

P6 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

ReEvP6:

''To Exclude characters (S, L, O, I, B and Z) for Position 6

If P6 = "S" Or P6 = "L" Or P6 = "O" Or P6 = "I" Or P6 = "B" Or P6 = "Z" Then

        P6 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP6

End If

End If

''******************************************************************

''To Exclude characters (S, L, O, I, B and Z) for Type A in the other positions

ReEvP2:

If P2 = "S" Or P2 = "L" Or P2 = "O" Or P2 = "I" Or P2 = "B" Or P2 = "Z" Then

        P2 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP2

End If

ReEvP5:

If P5 = "S" Or P5 = "L" Or P5 = "O" Or P5 = "I" Or P5 = "B" Or P5 = "Z" Then

        P5 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP5

End If

ReEvP8:

If P8 = "S" Or P8 = "L" Or P8 = "O" Or P8 = "I" Or P8 = "B" Or P8 = "Z" Then

        P8 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP8

End If

ReEvP9:

If P9 = "S" Or P9 = "L" Or P9 = "O" Or P9 = "I" Or P9 = "B" Or P9 = "Z" Then

        P9 = Chr(Application.WorksheetFunction.RandBetween(65, 90))

        GoTo ReEvP9

End If

'''''''''''''''''''''''''''''''''''''''''''''''''''Formula Result''''''''''''''''''''''''''''''''''''''''''''''''''

RandomMBIs = P1 & P2 & P3 & P4 & P5 & P6 & P7 & P8 & P9 & P10 & P11

End Function

*********************************************************************************************************

And follow the steps in pictures below.

**********************************************************************************************

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, I'll appreciate you mark it as answered

Please Note:

By marking a Reply as a "Accept as Solution", if it solves your problem. Will give positive reinforcement, not only to the person who responded, but also will help other users with the same or a similar question by directing them to the response.Thanks for your co-operation.