Combine two cells in Excel 2016 and add zeros and as well.



I would like to add two cells together and also add zeros with a forward slash ( / ).

DABB0107DD001   (CELLA1 )

1                               (CELL A2)

I would like to add cells A1 & A2 into A3 to read/formatted as follows:





Selected Answer

formula fo cell A3  "=LEFT(A1,6)&"-"&MID(A1,7,2)&"-"&MID(A1,9,1)&"-"&MID(A1,10,5)&"/"&TEXT(A2,"0000") "  [without quotes]'s same formula in your previous post, with the addition of Cell A2  (where I use the Text formula to format 4 placeholders


Here is an alternative approach, using a UDF (User-Defined Function). The advantage is that it takes much less space, it's a lot easier to understand, modify and maintain, and it's more robust when dealing with worksheet mistakes. The disadvantage might be that you need to keep your workbook macro-enabled. Some companies don't like that. You may like to try it out.

First, this is how you call the function from your worksheet.

= CaseID(A1, A2)

You can use the referencing as you are used to, absolute or relative. You can place the formula anywhere you want, on any of your sheets, and copy it the way you would copy other formulas. You can change the name of the function.

The following code must be in a standard code module. Press Alt+F11 to open the VB Editor, right-click on "VBA Project (name of your workbook)" in the Project Explorer window on top the left. (Press Ctl+R if the window isn't there by itself.) Select 'Insert' and 'Module'. VBA will insert a standard code module and select it. Paste the code into this module (on the right part of the screen). That's all you need to do. The function is now available in that workbook.

Function CaseID(Cell As Range, Seq As Long) As String
    Dim Fun As String
    Dim Id As String
    Dim n As Integer
    Dim Groups
    Dim i As Integer
    ' Explanation
      ' insert a dash after each group of characters:
    ' First group 6, next group 2, then 1, followed by remaining characters
      ' You can modify the numbers and make as many groups as you wish
    Groups = Array(6, 2, 1)
    n = 1
    Id = Trim(Cell.Value)
    If Len(Id) Then
        For i = 0 To UBound(Groups)
            Fun = Fun & Mid(Id, n, Groups(i)) & "-"
            n = n + Groups(i)
        Next i
    End If
    CaseID = Fun & Mid(Id, n) & Format(Seq, """/""0000")
End Function

At the top of the code you see 'Function CaseID', and in the last line you see 'CaseID = Fun ...'. If you wish to change the functions name you must replace "CaseID" in these two places, then call it under the new name from your worksheet.

The "secret" of this function is in the code line

Groups = Array(6, 2, 1)
You don't need to learn this by heart because the explanation is right there in the code whenever you need to know, now or after five years. If you are using an Excfel version 2007 or later remember to save thw wrokbook in XLSM format (macro-enabled).

Answer the Question

You must create an account to use the forum. Create an Account or Login