Convert Bar and Comma Separated Values into a single row with one record per cell...

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
I have example data that is structured as the below with aggregated values on the end in one cell. I'm hoping to split this out into each record if it is split with a comma, space or pipe. The intended output of it would be to use vba to show it the column with the separated values and then have it output in a separate column with one each. I found an example of something very similar online but it did not work on my 50,000+ record set. Some of the rows contain aggregated records of up to 200 as well.


Starting Example:


ItemChoices
AAAA101010101010, 2020202044, 21203991911 | 23923277723
BBBB552532535232, 73746463434, 34934838434 | 4545787343
CCCC766626222 | 4972329832




End Result Needed:
AAAA101010101010
AAAA2020202044
AAAA21203991911
AAAA23923277723
BBBB552532535232
BBBB73746463434
BBBB34934838434
BBBB4545787343
CCCC766626222
CCCC4972329832
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Below macro to set up long Formula array

Code:
Sub Separated_Values()

Application.ScreenUpdating = False
Dim Rng1 As Range, Rng2 As Range
Dim Adrs1 As String, Adrs2 As String
Dim MID1 As String, Mid1A As String, Mid2 As String, StrtNum1 As String, StrtNum2 As String, NumChr1 As String, NumChr2 As String, FndStrtNum1 As String, FndStrtNum2 As String


Set Rng1 = Range("A2:C4") '<<<<<
Set Rng2 = Range("D2:D4") '<<<<<

Adrs1 = Rng1.Address(True, True)
Adrs2 = Rng2.Address(True, True)

MID1 = "SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Mid1A,""|"","",""),"";"","",""),"","","";,"")"
Mid1A = "MID("";""&TEXTJOIN("";"",1," & Adrs2 & ")&"";"",StrtNum1,NumChr1)"
StrtNum1 = "FIND(""|"",SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""|"",TRANSPOSE(ROW(INDIRECT(""1:""&LEN("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"")-LEN(SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""""))-1)))))"

FndStrtNum1 = "TRANSPOSE(ROW(INDIRECT(""2:""&LEN("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"")-LEN(SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"","""")))))"
NumChr1 = "FIND(""|"",SUBSTITUTE("";""&SUBSTITUTE(TEXTJOIN("";"",1," & Adrs2 & "),""|"","","")&"";"","";"",""|"",FndStrtNum1))-StrtNum1"

Mid2 = "MID(""""&TEXTJOIN("","",1," & Adrs1 & ")&"","",StrtNum2,NumChr2)"
StrtNum2 = "TRANSPOSE(FIND(""|"",SUBSTITUTE("",""&TEXTJOIN("","",1," & Adrs1 & ")&"","","","",""|"",(ROW(INDIRECT(""1:""&ROWS(" & Adrs1 & ")))-1)*COLUMNS(" & Adrs1 & ")+1),(ROW(INDIRECT(""1:""&ROWS(" & Adrs1 & ")))-1)*COLUMNS(" & Adrs1 & ")+1))"
FndStrtNum2 = "(ROW(INDIRECT(""2:""&ROWS(" & Adrs1 & ")+1))-1)*COLUMNS(" & Adrs1 & ")+1"
NumChr2 = "TRANSPOSE(FIND(""|"",SUBSTITUTE("",""&TEXTJOIN("","",1," & Adrs1 & ")&"","","","",""|"",FndStrtNum2),FndStrtNum2))-StrtNum2"

Rws = [LEN(SUBSTITUTE(SUBSTITUTE(";"&TEXTJOIN(";",1,$D$2:$D$4)&"",",",";"),"|",";"))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(";"&TEXTJOIN(";",1,$D$2:$D$4)&"",",",";"),"|",";"),";",""))]
Rw = Rng1.Row + Rng1.Rows.Count + 3
cl = Rng1.Column
With Cells(Rw, cl).Resize(Rws, Rng1.Columns.Count + 1)

.FormulaArray = "=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(SUBSTITUTE(MID1,"","",MID2)),"";"","""",1),"";"",REPT("" "",999)),(ROW()-ROW(A$8)+1)*999-998,999)),"","",REPT("" "",999)),(COLUMN()-COLUMN(A$8)+1)*999-998,999))"
.Replace "MID1", MID1
.Replace "Mid1A", Mid1A
.Replace "StrtNum1", StrtNum1
.Replace "NumChr1", NumChr1
.Replace "FndStrtNum1", ndStrtNum1
.Replace "StrtNum1", StrtNum1

.Replace "MID2", Mid2
.Replace "StrtNum2", StrtNum2
.Replace "NumChr2", NumChr2
.Replace "FndStrtNum2", FndStrtNum2
.Replace "StrtNum2", StrtNum2

Application.ScreenUpdating = True

End With

End Sub




select 13 Rows and 4 Columns then Press CTRL+SHIFT+ENTER to enter array formulas.

nnnnn.xlsm
ABCD
1ItemItem detailItem DetailItem Agg
2AAAAsqsqsqplpl1111111, 22222222, 3333333333 | 23232323232 | 777
3BBBBGHGHNYNY1111111, 22222222, 3333333333 | 23232323232
4CCCCGHGHKTKT1111111, 22222222, 3333333333 | 23232323232
5
6
7ItemItem detailItem DetailItem Agg
8AAAAsqsqsqplpl1111111
9AAAAsqsqsqplpl22222222
10AAAAsqsqsqplpl3333333333
11AAAAsqsqsqplpl23232323232
12AAAAsqsqsqplpl777
13BBBBGHGHNYNY1111111
14BBBBGHGHNYNY22222222
15BBBBGHGHNYNY3333333333
16BBBBGHGHNYNY23232323232
17CCCCGHGHKTKT1111111
18CCCCGHGHKTKT22222222
19CCCCGHGHKTKT3333333333
20CCCCGHGHKTKT23232323232
21
Sheet1
Cell Formulas
RangeFormula
A8:D20A8=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(";"&TEXTJOIN(";",1,$D$2:$D$4)&";",FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";",""))-1))))),FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("2:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","")))))))-FIND("|",SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";")-LEN(SUBSTITUTE(";"&SUBSTITUTE(TEXTJOIN(";",1,$D$2:$D$4),"|",",")&";",";",""))-1)))))),"|",","),";",","),",",";,"),",",MID(""&TEXTJOIN(",",1,$A$2:$C$4)&",",TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1)),TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("2:"&ROWS($A$2:$C$4)+1))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("2:"&ROWS($A$2:$C$4)+1))-1)*COLUMNS($A$2:$C$4)+1))-TRANSPOSE(FIND("|",SUBSTITUTE(","&TEXTJOIN(",",1,$A$2:$C$4)&",",",","|",(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1),(ROW(INDIRECT("1:"&ROWS($A$2:$C$4)))-1)*COLUMNS($A$2:$C$4)+1))))),";","",1),";",REPT(" ",999)),(ROW()-ROW(A$8)+1)*999-998,999)),",",REPT(" ",999)),(COLUMN()-COLUMN(A$8)+1)*999-998,999))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,136,284
Messages
5,674,841
Members
419,530
Latest member
undisclosed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top