All data from one cell to other rows and columns

[ccgbalarin]

Hi everyone!



I created a purchase order request from Jotform, but when I download the Excel file, all the information from the products come in one cell.

I need the information to be allocated on separated columns and rows (example in the image below).

I've tried using the replace tool + text to columns but it doesn't work for me, it only register one paragraph from the text I receive. It is important to notice that the different products are separated by paragraphs (by pressing Enter).

This is the ideal layout for me:

(if we could "merge&center" the empty cells would be nice, but not needed)

If I'm able to achieve this, I can use "Text to Columns" to look like this:

And this is the end result I want.



It needs to be done automatically for me (Macro and/or VBA), because this file keeps updating after 15 days.



Any ideas on how to do this?



Thanks in advance,

Caio

rawfile_purchaseorderform.xlsx
	A	B	C	D	E	F	G
1	Submission Date	Approval Status	Identificação Única	Nome	Sobrenome	E-mail	Itens
2	2021/11/18 10:08:39	Aprovado	P-00003	ian	nepomniachtchi	nepo@gmail.com	Pincel Hidrográfico Compactor (Amount: 0.00 BRL, Quantidade: 6) Papel Celofane (Amount: 0.00 BRL, Quantidade: 2, Cor: Azul Escuro) Papel Celofane (Amount: 0.00 BRL, Quantidade: 3, Cor: Laranja) Envelope Comercial (Amount: 0.00 BRL, Tipo: Com CEP, Quantidade: 1) Etiqueta (Amount: 0.00 BRL, Por Folha: 16 unidades, Quantidade: 2) Placa EVA Glitter (Amount: 0.00 BRL, Quantidade: 3, Cor: Azul Escuro) Placa EVA Glitter (Amount: 0.00 BRL, Quantidade: 4, Cor: Branco) Placa EVA Glitter (Amount: 0.00 BRL, Quantidade: 6, Cor: Rosa) Lantejoula Tubo (Amount: 0.00 BRL, Quantidade: 5, Cor: Vermelho, Tamanho: Pequena) Lantejoula Tubo (Amount: 0.00 BRL, Quantidade: 2, Cor: Verde Claro, Tamanho: Pequena) Total: 0.00 BRL
3	2021/11/11 14:17:18	Aprovado	P-00001	caio	caio	caio@caio.com	Marca Texto (Amount: 0.00 BRL, Quantidade: 1, Cor: Amarelo) Marca Texto (Amount: 0.00 BRL, Quantidade: 2, Cor: Rosa) Tinta para Reabastecimento de Pincel (Amount: 0.00 BRL, Quantidade: 2, : Vermelho) Total: 0.00 BRL
Sheet1

 

[mackc557]

Try this code. You will need to select the first cell with data to split (in the case of your example it would be G2), then simply run the macro. It will run through all rows below it until it hits an empty row.

As always I tell people to try it on a sample set or a copy of the file so as not to lose your data.

If there is anything you would like me to change, let me know.

Sub Split()
    Dim sheet As Worksheet
    Dim cell As Range
    Dim Text As String
    Dim count As Integer
    Dim nextcell As Range
    Dim cutoff As Boolean
    Dim curCell As Range
    
    Set sheet = ThisWorkbook.ActiveSheet
    Set cell = Application.ActiveCell
    
    'Set Currently Used Cell
    Set curCell = Cells(cell.Row, cell.Column)
    
    'Check if there are multiple rows to process
    If Cells(cell.Row + 1, cell.Column).Value <> "" Then
        Set nextcell = curCell.Offset(1)
        cutoff = False
    Else
        cutoff = True
    End If
    
    'Main Loop
    For x = cell.Row To cell.End(xlDown).Row
        Text = curCell.Value
        count = Len(Text) - Len(Replace(Text, ")", ""))
        For i = 1 To count
            If i < count Then
                Rows(curCell.Offset(i).Row).Insert
            End If
            curCell.Offset(i - 1).Value = Left(Text, InStr(Text, ")"))
            Text = Right(Text, Len(Text) - InStr(Text, ")"))
        Next i
        'Mov
        If cutoff = False Then
            Set curCell = nextcell
            Set nextcell = nextcell.Offset(1)
        End If
    Next x
End Sub