Alphanumeric cell format (using custom format)

6,194

This Macro will do it for you.

This will create the result in a new column. So, my version looks at my column A which contains the in-correctly formatted content (EG W/1/1). I have nominated to have column B display the new result.

So, make sure you have the developer ribbon and draw a button from the Controls pane. When it asks you assign macro, click New. Paste the following into the new Window over the top (deleting everything that was there and assuming that your macro name is Button1_Click)

Sub Button1_Click()

Dim cellRow As Integer
cellRow = 1           'UPDATE THIS IF YOUR FIRST ROW DOESN'T START AT ROW 1

For Each c In Range("A1:A2") ' UPDATE THIS FOR ALL CELLS
Dim splitString() As String
splitString = Split(c.Value, "/")

Dim result As String
result = splitString(0)

If (Len(splitString(1)) = 5) Then

    result = result + "\" + splitString(1)

Else

    Dim i As Integer
    i = 5 - Len(splitString(1))
    result = result + "\"
    For x = 0 To i - 1
        result = result + "0"
        i = i + 1
    Next x
        result = result + splitString(1)
End If


If Len(splitString(2)) = 4 Then
    result = result + "\" + splitString(2)
Else

    Dim j As Integer
    j = 4 - Len(splitString(2))
    result = result + "\"
    For y = 0 To j - 1
        result = result + "0"
        j = j + 1
    Next y

result = result + splitString(2)
End If



Range("A" + CStr(cellRow)).Value = result ' UPDATE FROM A IF YOU DON'T WANT IT TO OVEWRITE EXISTING CONTENT 
cellRow = cellRow + 1

Next
End Sub

Now, this will crash if you have empty cells or anything which doesn't exist in the formatted you specified, eg WT\456789456/12345AL will not perform as desired

But, this doesn't really care about the value being Alpha or numeric, it just expects the initial value to be the desired character type.

Update

The code can be made shorter as per @SeanCheshire excellent comment

A shortcut to make a 5 character zero padded string would be

right("00000" & splitstring(1),5) 

No loops or extra variables needed. Your entire string construct would be

splitstring(0) & "\" & Right("00000" & splitstring(1), 5) & "\" & Right("00000" & splitstring(2), 5) 
Share:
6,194

Related videos on Youtube

Saram
Author by

Saram

Updated on September 18, 2022

Comments

  • Saram
    Saram over 1 year

    I want to display an alphanumeric code in this format:

    A/DDDDD/DDDD

    Where A is letter and D is digit. If there are less digits, a leading 0 should be displayed.

    Example:
    W/1/1 displays: W/00001/0001
    Z/12345/1234 displays: Z/123345/1234
    W/123/123 displays: W/00123/0123

    The value in cell should persist in given format (with out leading 0).

    • Jerry
      Jerry over 10 years
      You cannot format alphanumeric values in excel; only numeric.
    • Saram
      Saram over 10 years
      my foul. changed. thx
  • Dave
    Dave over 10 years
    @Saram In that case, you can just overwrite it... Just change the 4th from last line to an A instead of a B. I updated my code and this will do it for you. However, this is dangerous so I do suggest you make sure you have a back up first!
  • Saram
    Saram over 10 years
    does it fulfill this: "The value in cell should persist in given format (with out leading 0)."?
  • Saram
    Saram over 10 years
    @DaveRook I did not test it, because I can read code and I noticed that it can be problem. Now I tested it, and I confirm. This does not work as expected.
  • Dave
    Dave over 10 years
    What does that even mean? "the value in cell should persist..."
  • Saram
    Saram over 10 years
    When you type W/1/1 it should be displayed as: W/00001/0001, but when you back to edit it again the original 'W/1/1' value should appears. Just like numeric custom formatting behavior. I hope I'm clear, but please forgive me my English, I'm not native.
  • Dave
    Dave over 10 years
    Right... (@saram, your English is excellent). You can't do this without some form of persistence. So, I think you'd have make the code copy the initial value, stick it in another work sheet and have another button to 'revert' back (or at least, revert back on close).