Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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).

share|improve this question
    
You cannot format alphanumeric values in excel; only numeric. –  Jerry Oct 1 '13 at 9:38
    
my foul. changed. thx –  Saram Oct 1 '13 at 10:35

1 Answer 1

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|improve this answer
    
I can not have this displayed two times due to print accurate formatting. My current solution is to have 3 cells: | W|/00012|/0002 but customer wants it in one field. –  Saram Oct 1 '13 at 10:38
    
@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! –  Dave Oct 1 '13 at 10:48
2  
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) –  Sean Cheshire Oct 1 '13 at 12:18
    
does it fulfill this: "The value in cell should persist in given format (with out leading 0)."? –  Saram Oct 2 '13 at 7:21
    
@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. –  Saram Oct 2 '13 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.