Excel - Formula based transpose from comma separated values to individual rows

Question

I maintain my CD/DVD inventory in an Excel file (Excel 2013). The data is organized as shown in below snapshot.

On each row there’s first cell mentioning the CD label and next cell holds the comma separated list of apps that I have written on it.

However I am interested in a view in which the comma separated values get transposed to rows so that there’s only one item per cell and per row as shown below.

I understand, VBA can help here easily and I do have basic VBA knowledge, however is there a possible formula based solution to transpose the data from input to output? I want to avoid Macros and also want to avoid manual process of Text To Column then Copy-Paste Transpose.

It should also trim the extra space if any after comma.

Solution 1

To do it with a formula, with older than Office 365 Excel you will need a helper column.

Next to your comma delimited list(my list starts in C2) put:

=LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1+D1

This will keep a running total of the number of words.

Then you reference both these columns with INDEX/MATCH to reference the correct cell as the long formula gets dragged down.

The long formula is:

=TRIM(MID(SUBSTITUTE(INDEX(C:C,MATCH(ROW(1:1),D:D)),",",REPT(" ",99)),(ROW(1:1)-INDEX(D:D,MATCH(ROW(1:1),D:D)))*99 +1,99))

Then two INDEX/MATCH returns the correct cells to be processed.

The SUBSTITUTE adds a lot of white space giving a large target for the Mid to find.

The Mid splits the long string in the white space.

The TRIM removes the extraneous white space.

---

With Office 365 you can skip the helper column and use TEXTJOIN() if the string is not too long.

=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$C$2:$C$5),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99))

This replaces the INDEX/MATCH with TEXTJOIN, otherwise it acts like above.

Solution 2

I would like to suggest one simple VBA (Macro) method, which is very easy to use & better than any tedious formula.

Private Sub CommandButton1_Click()

    Dim xArr() As String
    Dim xAddress As String
    Dim Rg As Range
    Dim Rg1 As Range

    On Error Resume Next

    xAddress = Application.ActiveWindow.RangeSelection.Address

    Set Rg = Application.InputBox("please select the data range:", "Column to Row", xAddress, , , , , 8)

    If Rg Is Nothing Then Exit Sub

    Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)

    If Rg Is Nothing Then Exit Sub

    Set Rg1 = Application.InputBox("please select output cell:", "Column to Row", , , , , , 8)

    If Rg1 Is Nothing Then Exit Sub
    xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
    Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
    Rg1.Parent.Activate
   Rg1.Resize(UBound(xArr) + 1).Select

Call TrimXcessSpaces

End Sub

How it works:

• Click Developer TAB, hit Design then Insert.
• Select Command button from ActiveX category and draw anywhere on sheet.
• Right Click Command button and Select Property.
• Find Caption From the list and change it to Column To Row.
• Close Property Window & return to Sheet.
• Double Click the Command button, you will land to VB Editor Window.
• Copy & Paste this Code in between Private Sub CommandButton1_Click() and End Sub and click Save Icon.
• From VB editor window click File Command and click Close & Return to Microsoft Excel.
• Click the Design Mode to turn it off.

Now Click the on Sheet command button:

• First Input Box will appear then select List separated by comma in (B4:B6) & finish with Ok.

• Second Input Box will appear select any single Cell like I did E4 & finish with Ok button.

You find the list in Rows as shown in Screen Shot.

N.B. Adjust cell references as needed.

EDITED:

I've edited the post since PeterH has suggest me to remove White Space from the output in Column E.

1. Form the Sheet Press ALT+F11, to open the VB Editor.
2. Find the Sheet name from Project Explorer Windows on left.
3. Select & Right click the Sheet name.
4. Find Insert from menu then click the Module command.

5. Copy & Paste this code & Save to return the Sheet.

    Option Explicit
   
    Sub TrimXcessSpaces()
   
     Dim cl As Variant
   
     For Each cl In Selection
        If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
            cl.Value = WorksheetFunction.Trim(cl)
        End If
     Next cl
   End Sub
   

Note, add this code just before the End Sub in previous code,(check the code above).

Call TrimXcessSpaces

6. From the Sheet Click the Command button, output will look like shown below.

Comments

[PeterH]

there probably is some sort of array that would work on your small data set, but if you have a much larger data set to work with I think you will struggle to find a solution without VBA

[rajeev]

There are about 40 rows of data right now. Rows get added as and when a new optical disk is used.

[Rajesh Sinha]

@rajeev, instead of a Formula I found MACRO (VBA) is the best suitable & easier method to handle the issue. As you can find in comments PeterH has also suggested the VBA solution. Therefore I've posted the code which is easy to use and could easily handle small as well as Larger INPUT comma separated rows. It's handy and, I do believe you find it quit useful too.

[Rajesh Sinha]

Thanks @PeterH,, LOL , while commenting I really missed the code to include. Now it's been added. Thanks you prompted me. :-)

[PeterH]

+1 from me, great answer, only tiny thing missing is that spaces are still included in the output

[Rajesh Sinha]

Thanks @PeterH, for appreciating my efforts,, let me check how to adjust the spaces.

[Rajesh Sinha]

@PeterH, just check the post I've edited to remove the White Spaces from Output.