# Transform data from rows to columns

#### cuinbc

##### New Member
[FONT=&quot]Hi all,

My raw data looks like this:[/FONT]

[FONT=&quot]The data was copied and pasted from a notepad, so each line is on a different row.

[/FONT]

====Starting details=====
20: abc
23B: def
32A: ghk
50K: lmn
=====Ending details=====
====Starting details=====
20: 123
23B: 321
32A: 456
50K: 789
=====Ending details=====
====Starting details=====
20: qwe
23B: wer
32A: ert
50K: rty
=====Ending details=====

</tbody>
[FONT=&quot]
[/FONT]

• Please note that in reality, the details that follow the (20:, 23B:, 32A: 50K are all different lengths, but the details always follow 20:/23B:/32A:/50K:
• Text to column won't work because the data is scattered on different rows.
[FONT=&quot]I need to achieve the below:[/FONT]
20:23B:32A:50K:
abcdefghklmn
123234456789
qwewerertrty

</tbody>
[FONT=&quot]- That is, basically, extract text following each common of "20:", "23B:" "32A" & "50K", and organize them to columns.[/FONT]

• First of all, should I figure out a way to combine rows between the common texts "====Starting details=====" & "=====Ending details====="? It would become something like this:
20: abc 23B: 321 32A: 456 50K: 789

[FONT=&quot]2) Once step 1 is solved, I can figure out a way to extract text between 20:/23B:/32A:/50K:, and place it on different columns?[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]Please let me know if this is feasible? What formulas/tools on power query should I utilize?[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]Open to any ideas.[/FONT]

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### sandy666

##### Well-known Member
Is that what you want?

done with PowerQuery (Get&Transform)
btw. this is not vba

 SOURCE RESULT Column1 20: 23B: 32A: 50K: ====Starting details===== abc def ghk lmn 20: abc 123 321 456 789 23B: def qwe wer ert rty 32A: ghk 50K: lmn =====Ending details===== ====Starting details===== 20: 123 23B: 321 32A: 456 50K: 789 =====Ending details===== ====Starting details===== 20: qwe 23B: wer 32A: ert 50K: rty =====Ending details=====

M-Code
Code:
``````[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "=====Ending details=====" and [Column1] <> "====Starting details=====")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column1.1"}, {{"Count", each _, type table}}),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Count"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
in

#### Peter_SSs

##### MrExcel MVP, Moderator
I know you mentioned Power Query and you have a suggestion for that, but in case it is of interest to you, following are suggestions for vba and formulas to do the job assuming the data is uniform like your samples.

1) VBA
(Assuming data in col A starting at row 1, & results into columns B:E)
Code:
``````Sub Rearrange()
Dim a As Variant, b As Variant
Dim i As Long, j As Long, k As Long

a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 4)
For i = 1 To UBound(a)
If a(i, 1) Like "====S*" Then
k = k + 1
For j = 1 To 4
b(k, j) = Split(a(i + j, 1), ": ", 1)(1)
Next j
i = i + 5
End If
Next i
Range("B2:E2").Resize(k).Value = b
Range("B1:E1").Value = Array("'20:", "23B:", "32A:", "50K:")
End Sub``````

2) Formula, copied across and down.

Excel Workbook
ABCDE
1====Starting details=====20:23B:32A:50K:
220: abcabcdefghklmn
323B: def123321456789
432A: ghkqwewerertrty
550K: lmn
6=====Ending details=====
7====Starting details=====
820: 123
923B: 321
1032A: 456
1150K: 789
12=====Ending details=====
13====Starting details=====
1420: qwe
1523B: wer
1632A: ert
1750K: rty
18=====Ending details=====
Sheet1

Replies
6
Views
411
Replies
3
Views
847
Replies
1
Views
411
Replies
14
Views
377
Replies
3
Views
160

1,109,395
Messages
5,528,487
Members
409,820
Latest member
gabrielrms

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...