VBA code to split a string into a table

[Excelpromax123]

Hi everyone, I need to split a string into a table as the picture describes. ask someone for help. Thank you

text new.xlsx

Sheet1 INPUT,OUPUT TEXT 1,TEXT 2,Data,TEXT 1,TEXT 2,Data XXX,1,A / A / CC / DDD /,XXX,1,A YYYYY,222,AA / BB / CC / CC / EE / DD / FF / GG /,XXX,1,A XXX,1,CC XXX,1,DDD YYYYY,222,AA YYYYY,222,BB YYYYY,222,CC YYYYY,222,CC YYYYY,222,EE YYYYY,222,DD YYYYY,222,FF YYYYY,222,GG

docs.google.com

 

[Herakles]

Give this a go.

Public Sub subSplitString()
Dim arr() As String
Dim rng As Range
    
    For Each rng In Range("D3", Cells(Rows.Count, "D").End(xlUp)).Cells
            
        arr = Split(Trim(Replace(Replace(rng.Value, " / ", " ", 1), "/", "", 1)), " ")
   
        With Cells(Rows.Count, "I").End(xlUp).Offset(1).Resize(UBound(arr) + 1, 1)
            .Value = Application.Transpose(arr)
            .Offset(0, -2).Value = rng.Offset(0, -2).Value
            .Offset(0, -1).Value = rng.Offset(0, -1).Value
        End With

    Next rng

End Sub

 

[alansidman]

An alternative solution is with Power Query

text new.xlsx
	B	C	D	E	F	G	H
1	INPUT
2	TEXT 1	TEXT 2	Data		TEXT 1	TEXT 2	Data
3	XXX	1	A / A / CC / DDD /		XXX	1	A
4	YYYYY	222	AA / BB / CC / CC / EE / DD / FF / GG /		XXX	1	A
5					XXX	1	CC
6					XXX	1	DDD
7					YYYYY	222	AA
8					YYYYY	222	BB
9					YYYYY	222	CC
10					YYYYY	222	CC
11					YYYYY	222	EE
12					YYYYY	222	DD
13					YYYYY	222	FF
14					YYYYY	222	GG
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Data", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Data", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Data] <> ""))
in
    #"Filtered Rows"

 

[Excelpromax123]

I ask you to correct the code at this line :

arr = Split(Trim(Replace(Replace(rng.Value, " / ", " ", 1), "/", "", 1)), " ")

My data IS THIS EXAMPLE THE CODE is running wrong

A -1 / A -2 / CC / DDD /
Thank you

 

[alansidman]

Have you tested the Mcode I supplied in post #3?