title: Better method to Split Cell values in multiple Rows and Concatenate these values in the next Column with formatting intact using Excel-VBA
body: <p>Here is the view of my excel sheet:
  <a href="https://i.stack.imgur.com/MNOip.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MNOip.jpg" alt="enter image description here"></a></p>
  
  <p><strong>Document Overview:</strong></p>
  
  <p>It has multiple columns which may vary as per the requirement.Document ID's and Document Versions are always available in each sheet, however the Name of the Column (Ex; Doc ID or ID / Doc Version or Doc #) and Columns (Ex; Column G &amp; H / Column J &amp; K) may vary.</p>
  
  <p>In this case, Document ID - Column C and Document version - Column D may contain multiple values in each cell.</p>
  
  <p>Document Id always has 9 digits (filled with trailing zeros if the Id does not have enough digits). Ex; <strong>000</strong>987094, 123456100 , 234567899, <strong>0</strong>23456789 etc.</p>
  
  <p>Document Version always has the fixed format of "0.0" or "00.0", Ex; 1.0, 23.0, 2.1 etc.</p>
  
  <p><strong>Description of what I have done so far:</strong></p>
  
  <p>I use VBA Macro to split the Cells containing multiple values (ID and related Versions, highlighted in the uploaded image) into rows below them.  After that I Concatenate the Split Values into next column by inserting a new column manually and then using another Macro to Concatenate. </p>
  
  <p>Here is the Output after I run the Macro:</p>
  
  <p><a href="https://i.stack.imgur.com/BPuf4.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BPuf4.jpg" alt="enter image description here"></a></p>
  
  <p><strong>Macros:</strong></p>
  
  <pre><code>    Sub SplitCellValuesIntoRows()
  
          Dim rng_all_data As Range
          'Set rng_all_data = Application.InputBox(prompt:="Select the Range of cells:", Type:=8)
          Set rng_all_data = ActiveSheet.UsedRange
          Dim int_row As Integer
          int_row = 0
  
          On Error Resume Next
  
          Dim sht_out As Worksheet
          Set sht_out = Worksheets.Add
  
          Dim rng_row As Range
          For Each rng_row In rng_all_data.Rows
  
              Dim int_col As Integer
              int_col = 0
  
              Dim int_max_splits As Integer
              int_max_splits = 0
  
              Dim rng_col As Range
              For Each rng_col In rng_row.Columns
  
                  Dim col_parts As Variant
                  col_parts = Split(rng_col, vbLf)
  
                  If UBound(col_parts) &gt; int_max_splits Then
                      int_max_splits = UBound(col_parts)
                  End If
  
                   sht_out.Range("A1").Offset(int_row, int_col).Resize(UBound(col_parts) + 1) = Application.Transpose(col_parts)
  
                  int_col = int_col + 1
              Next
  
              int_row = int_row + int_max_splits + 1
          Next
  
      End Sub
  
  
  
  Sub Join_em()
  
      For i = 2 To ActiveSheet.UsedRange.Rows.Count
          Range("E" &amp; i).Formula = (Range("C" &amp; i).Value &amp; " " &amp; Range("D" &amp; i).Value)
      Next i
  
  End Sub
  </code></pre>
  
  <p>In the macro Join_em(), I fill the values manually after using the first Macro SplitCellValuesIntoRows(), based on the output columns of Document ID and Document Version to get the Concatenated values. </p>
  
  <pre><code>Range("E" &amp; i).Formula = (Range("C" &amp; i).Value &amp; " " &amp; Range("D" &amp; i).Value)
  </code></pre>
  
  <p><strong>C, D</strong> and <strong>E</strong> in this case.</p>
  
  <p><strong>What I want to achieve:</strong></p>
  
  <p>I am trying to achieve something like this as the output:</p>
  
  <p><a href="https://i.stack.imgur.com/dNunZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dNunZ.jpg" alt="enter image description here"></a></p>
  
  <ol>
  <li>Split the Cells with multiple values by adding rows in the <strong>same sheet</strong> and keep the destination cell <strong>formatting intact</strong>.</li>
  <li><strong>Add a new Column</strong> E (in this case) and Concatenate the values from Document ID and Document Version <strong>with the leading and trailing zeroes</strong> intact.</li>
  <li>Since the format for Document ID (9 digits with/without trailing zeroes) and Document versions ("0.0" or "00.0") are always fixed, but the Name and Column # are not fixed, Is it possible to use regex and combine the respective cells into newly inserted column beside them automatically after splitting multiple cell values into individual rows. (It would be awesome to get to know how to make it work,I have tried it without success. I don't know the logic to make it work)</li>
  </ol>
  
  <p>Here is the link for downloading the dummy Excel Sheet, in case it is needed for clarity.</p>
  
  <p><a href="https://drive.google.com/file/d/14CQfDEv9uqxT21iMd-JbEhNLpvr9YP4o/view?usp=sharing" rel="nofollow noreferrer">DummyBook.xlsx</a></p>