Alphanumeric cell format (using custom format)
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)
Related videos on Youtube
Saram
Updated on September 18, 2022Comments
-
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 over 10 yearsYou cannot format alphanumeric values in excel; only numeric.
-
Saram over 10 yearsmy foul. changed. thx
-
-
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 over 10 yearsdoes it fulfill this: "The value in cell should persist in given format (with out leading 0)."?
-
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 over 10 yearsWhat does that even mean? "the value in cell should persist..."
-
Saram over 10 yearsWhen 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 over 10 yearsRight... (@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).