How to extract value within cells, and automatically convert them as new row?

[superjoejoe]

Hello,

1. There are multiple data within each cell
2. Each is separated by "|"
3. Need to extract each data & automatically generate new row
4. Final result is: each row contains 1 data only

Thank you

 

[sandy666]

with Power Query

Raw		Raw
http://url1.jpg | http://url2.jpg | http://url3.jpg | http://url4.jpg | http://url5.jpg		http://url1.jpg
http://url6.jpg | http://url7.jpg		http://url2.jpg
http://url8.jpg | http://url9.jpg | http://url10.jpg | http://url11.jpg		http://url3.jpg
http://url12.jpg		http://url4.jpg
		http://url5.jpg
		http://url6.jpg
		http://url7.jpg
		http://url8.jpg
		http://url9.jpg
		http://url10.jpg
		http://url11.jpg
		http://url12.jpg

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

 

[superjoejoe]

would try it, thank you for your help

 

[sandy666]

You are welcome

 

[Peter_SSs]

Welcome to the MrExcel board!

Each is separated by "|"

Your image looks more like the data is separated by " | " & my suggestions are based on the spaces being there. If they are not, modifications can be made.

Provided your data is not too large (TEXTJOIN has a limit for the resultant string of 32,767 characters) you could try this formula approach

20 07 03.xlsm
	A	B
1
2	http://url1.jpg | http://url2.jpg | http://url3.jpg | http://url4.jpg | http://url5.jpg	http://url1.jpg
3	http://url6.jpg | http://url7.jpg	http://url2.jpg
4	http://url8.jpg | http://url9.jpg | http://url10.jpg | http://url11.jpg	http://url3.jpg
5	http://url12.jpg	http://url4.jpg
6		http://url5.jpg
7		http://url6.jpg
8		http://url7.jpg
9		http://url8.jpg
10		http://url9.jpg
11		http://url10.jpg
12		http://url11.jpg
13		http://url12.jpg
14
Convert

Cell Formulas
Range	Formula
B2:B14	B2	=TRIM(LEFT(SUBSTITUTE(REPLACE("|"&TEXTJOIN(" | ",1,A$2:A$5)&" | ",1,FIND("#",SUBSTITUTE(" | "&TEXTJOIN(" | ",1,A$2:A$5)&" | "," | ","#",ROWS(B$2:B2))),"")," | ",REPT(" ",500)),500))

Alternatively, for the same layout, you could try this macro. This also has a limit - but much larger.

Sub SplitIt()
  Dim a As Variant
 
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = Split(Join(Application.Transpose(.Value), " | "), " | ")
    .Offset(, 1).Resize(UBound(a) + 1).Value = Application.Transpose(a)
  End With
End Sub

 

[superjoejoe]

Thank you for your help ^^

 

[Peter_SSs]

Cheers. Glad to help.

 

[Nabeel Gondal]

Another =TRIM(MID(SUBSTITUTE(TEXTJOIN("|",,$A$2:$A$5),"|",REPT(" ",255)),ROWS($C$4:C4)*255-254,255))

 

[Peter_SSs]

Another =TRIM(MID(SUBSTITUTE(TEXTJOIN("|",,$A$2:$A$5),"|",REPT(" ",255)),ROWS($C$4:C4)*255-254,255))

Without further modification, that one is limited. For example, we only have to add another row or two of data or even another few urls in an existing cell and strange results start to appear.

Example

20 07 03.xlsm
	A	B	C
1
2	http://url1.jpg | http://url2.jpg | http://url3.jpg | http://url4.jpg | http://url5.jpg
3	http://url6.jpg | http://url7.jpg
4	http://url8.jpg | http://url9.jpg | http://url10.jpg | http://url11.jpg		http://url1.jpg
5	http://url12.jpg | http://urla.jpg | http://urlb.jpg | http://urlc.jpg | http://urld.jpg		http://url2.jpg
6			http://url3.jpg
7			http://url4.jpg
8			http://url5.jpg
9			http://url6.jpg
10			http://url7.jpg
11			http://url8.jpg
12			http://url9.jpg
13			http://url10.jpg
14			http://url11.jpg
15			http://url12.jpg
16			http://urla.jpg
17			http://urlb.jpg
18			http://urlc.jpg
19			htt
20			p://urld.jpg
21
Convert (3)

Cell Formulas
Range	Formula
C4:C21	C4	=TRIM(MID(SUBSTITUTE(TEXTJOIN("|",,$A$2:$A$5),"|",REPT(" ",255)),ROWS($C$4:C4)*255-254,255))

You could increase the 255/254 values to extend somewhat but if the actual data is at all large you will then run the risk of exceeding TEXTJOIN's result character limit. Hence the approach that I took. The formula is a little longer but it will handle a much larger set of data.

 

[Nabeel Gondal]

@Peter_SSs yes, you are correct, modification will be required if there is large data set, not big it will be small!! thnx for pointing