single cell split in two cell and loop until find cell empty help in vba

[manojxls]

Hi Team, i need help on below sample excel vba
i have data in A column with two word in same cell i need to split in to two cell also amount need to divide half these should done until a column find blank cell.
ithe result should show as shown as right.

costcentre1 & costcentre2	400		costcentre1	=400/2
costcent4 & costcentr25	500		costcentre2	=400/2
			costcent4	=500/2
			costcentr25	=500/2

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" & ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Inserted Division" = Table.AddColumn(#"Split Column by Delimiter", "Division", each [Column2] / 2, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"Column2"})
in
    #"Removed Columns"

Book3
	A	B	C	D	E
1	Column1	Column2		Column1	Division
2	costcentre1 & costcentre2	400		costcentre1	200
3	costcent4 & costcentr25	500		costcentre2	200
4				costcent4	250
5				costcentr25	250
Sheet1

 

[manojxls]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" & ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Inserted Division" = Table.AddColumn(#"Split Column by Delimiter", "Division", each [Column2] / 2, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"Column2"})
in
    #"Removed Columns"

Book3
	A	B	C	D	E
1	Column1	Column2		Column1	Division
2	costcentre1 & costcentre2	400		costcentre1	200
3	costcent4 & costcentr25	500		costcentre2	200
4				costcent4	250
5				costcentr25	250
Sheet1

Hi Alan, thanks for sharing i dont find where is power query. but the result you share yes i want that. can you help me in vba or step where i paste code in power query.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[manojxls]

yes i found power query can you help me
below code i need for "&" ,"," -both character as some cell have & and some cell have 'couma

{"Column1", Splitter.SplitTextByDelimiter(" &,"," ", QuoteStyle.Csv)

 

[alansidman]

Replace with

    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" & ",","} QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),

 

[manojxls]

Replace with

    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" & ",","} QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),

getting error as
Token `'` expected.

 

[Cubist]

With 365 formula, try:

Book1
	A	B	C	D	E
1	costcentre1 & costcentre2	400		costcentre1	200
2	costcent4 & costcentr25	500		costcentre2	200
3				costcent4	250
4				costcentr25	250
Sheet2

Cell Formulas
Range	Formula
D1:E4	D1	=HSTACK(TEXTSPLIT(TEXTJOIN("&",TRUE,A1:A2),,"&"),INDEX(B1:B2,ROUNDUP(SEQUENCE(ROWS(A1:A2)*2)/2,0))/2)
Dynamic array formulas.

 

[alansidman]

With the following example, this Mcode works for me.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" & ",", "}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Inserted Division" = Table.AddColumn(#"Split Column by Delimiter", "Division", each [Column2] / 2, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"Column2"})
in
    #"Removed Columns"

Book4
	A	B	C	D	E
1	Column1	Column2		Column1	Division
2	costcentre1 & costcentre2	400		costcentre1	200
3	costcent4 & costcentr25	500		costcentre2	200
4	costcent5, costcent6	600		costcent4	250
5				costcentr25	250
6				costcent5	300
7				costcent6	300
Sheet1

My apologies, in my previous post, I was missing a comma. The above code is tested and works.

 

[manojxls]

thanks alansidman its works 95% i think i first need to clean data as many space,comma and & is there. thanks Cubist . many thanks all.