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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
A couple of questions...

1) When you say "...if it is split with a comma, space or pipe", do you mean sometimes there is only a space (no comma or pipe) between the numbers (none of your examples show this, hence the question)?

2) Where is the output to go... replace the original data in place or to a new pair of columns?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
ItemChoicesItemChoices
AAAA101010101010, 2020202044, 21203991911 | 23923277723AAAA101010101010
BBBB552532535232, 73746463434, 34934838434 | 4545787343AAAA2020202044
CCCC766626222 | 4972329832AAAA21203991911
AAAA23923277723
BBBB552532535232
BBBB73746463434
BBBB34934838434
BBBB4545787343
CCCC766626222
CCCC4972329832

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Choices", Splitter.SplitTextByAnyDelimiter({"|",","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Choices"),
    Trim = Table.TransformColumns(Split,{{"Choices", Text.Trim, type text}})
in
    Trim
 

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
A couple of questions...

1) When you say "...if it is split with a comma, space or pipe", do you mean sometimes there is only a space (no comma or pipe) between the numbers (none of your examples show this, hence the question)?

2) Where is the output to go... replace the original data in place or to a new pair of columns?
Apologies for neglecting to define those:

1) there should actually be no instance of a space as the sole delimiter, but I would just want the space removed if it was left remaining so it only held the value and not the trailing space
2) A new pair of columns would be ideal, so if those examples were in A, B the outputs would populate in C, D
 

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66

ADVERTISEMENT

ItemChoicesItemChoices
AAAA101010101010, 2020202044, 21203991911 | 23923277723AAAA101010101010
BBBB552532535232, 73746463434, 34934838434 | 4545787343AAAA2020202044
CCCC766626222 | 4972329832AAAA21203991911
AAAA23923277723
BBBB552532535232
BBBB73746463434
BBBB34934838434
BBBB4545787343
CCCC766626222
CCCC4972329832

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Choices", Splitter.SplitTextByAnyDelimiter({"|",","}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Choices"),
    Trim = Table.TransformColumns(Split,{{"Choices", Text.Trim, type text}})
in
    Trim
Unfortunately I think PQ is going to be lost on the end user here.
 

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66

ADVERTISEMENT

A handoff to someone temping to do a task but they have extremely minimal background. Juice not worth the squeeze kind of deal
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is a macro that you can consider...
VBA Code:
Sub Test()
  Dim Combined As Variant
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate("IF({1},A2:A4&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B4,"","","" ""),""|"","" "")),"" "","" ""&A2:A4&""#""))")))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub
NOTE: I outputted Column D as Text because some of your numbers are long and were displayed in exponential form and I am anticipating some of your numbers could possibly have leading zeros.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is a macro that you can consider...
VBA Code:
Sub Test()
  Dim Combined As Variant
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate("IF({1},A2:A4&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B4,"","","" ""),""|"","" "")),"" "","" ""&A2:A4&""#""))")))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub
NOTE: I outputted Column D as Text because some of your numbers are long and were displayed in exponential form and I am anticipating some of your numbers could possibly have leading zeros.
Whoops! I forgot to remove my test range address and generalize the solution for any number of cells (less that 65,500 or so maximum) in Column A. Here is the generalized solution...
VBA Code:
Sub Test()
  Dim LastRow As Long, Combined As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Combined = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF({1},A2:A@&""#""&SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(B2:B@,"","","" ""),""|"","" "")),"" "","" ""&A2:A@&""#""))", "@", LastRow))))))
  Range("C2").Resize(UBound(Combined)) = Combined
  Columns("C").TextToColumns , xlDelimited, , , False, False, False, False, True, "#", FieldInfo:=Array(Array(1, 1), Array(2, 2))
End Sub
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
A handoff to someone temping to do a task but they have extremely minimal background. Juice not worth the squeeze kind of deal
still don't understand
enough do that once and later just paste new data then refresh

but ok, your choice
have a nice day
 

Forum statistics

Threads
1,140,920
Messages
5,703,173
Members
421,279
Latest member
emzy

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