How to tabulate text file with data shown in blocks?

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
328
Office Version
  1. 365
Platform
  1. Windows
Good day,

I have an input file like this below in range A1:A98 (the actual data could have more than 5000 lines), for which I'd like to tabulate the info in the way I show in table (the numbers in red are only filled down from previous above). The parameters like Az to Gz are words with different length in actual file, here are smaller for example purposes.

Is there a way to do it with Excel formulas or only with VBA macro? Thanks for any help.


Sample File.xlsx
ABCDEFGHIJKLMNO
1SOME TEXT SOME TEXT SOME TEXTAzBzCzDzEzFzGzDRMRRCORDXFCDMBZHGW
2SOME TEXT15300
3RETCODE = 0153411
4328693
5 Az = 172290
6 Bz = 572214
7 Cz = 3722701
8 Dz = 072218
9 Ez = 02MAIN-332CTRRESDLAMPRM<NULL>XJNbbv.uj002.pkl706.svfdata
102MAIN-332CTRRESDLAMPRMALLOSTTbbv.uj003.pkl706.svfdata
11 Dz = 42MAIN-332CTRRESDLAMPRM<NULL>BNNbbv.uj410.pkl310.svfdata
12 Ez = 15GOPAGBRI5TTALDRdxFcdMbzbbv.123k.svfdata
13 Gz = 15GOPAGBRI5TTALDREJPRMRJCYWbbv.322h7.svfdata
145GOPAGBRI5TTALDPRM<NULL>G4bbv.119.svfdata
15
16blah blah
17
18
19--- ENDBLOCK
20
21SOME TEXT
22RETCODE = 0
23 Az = 3
24 Bz = 2
25 Cz = 8
26 Dz = 6
27 Ez = 9
28 Fz = 3
29
30
31
32blah blah
33
34blah blah
35
36--- ENDBLOCK
37
38SOME TEXT
39RETCODE = 0
40 Az = 7
41 Bz = 2
42 Cz = 2
43 Dz = 9
44 Ez = 0
45
46 Dz = 1
47 Ez = 4
48
49 Dz = 7
50 Ez = 0
51 Fz = 1
52
53 Dz = 1
54 Gz = 8
55
56
57blah blah
58
59blah blah
60
61--- ENDBLOCK
62%%XYZ HGGYT:%%
63
64BLA BLAH = 0
65
66 Bz = 2
67 Cz = MAIN-332
68 DRM = CTRRES
69 RRCO = DLAM
70
71blah blahblah blahblah blahblah blah
72-----------------------------------------------
73
74RDX FCD MBZ HGW
75PRM <NULL> XJN bbv.uj002.pkl706.svfdata
76PRM ALLO STT bbv.uj003.pkl706.svfdata
77PRM <NULL> BNN bbv.uj410.pkl310.svfdata
78
79
80blah blah
81
82--- ENDBLOCK
83%%XYZ HGGYT:%%
84
85BLA BLAH = 0
86
87 Bz = 5
88 Cz = GOPAG
89 DRM = BRI5T
90 RRCO = TALD
91
92blah blahblah blahblah blahblah blah
93-----------------------------------------------
94
95RDX FCD MBZ HGW
96Rdx Fcd Mbz bbv.123k.svfdata
97REJ PRMRJC YW bbv.322h7.svfdata
98PRM <NULL> G4 bbv.119.svfdata
SM
 

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).
Bring the text file into Power Query, manipulate it there, and output a nice, clean table. Looks like it's Tab Delimited.
 
Upvote 0
Bring the text file into Power Query, manipulate it there, and output a nice, clean table. Looks like it's Tab Delimited.
Thanks for answer, but I know very few about power query. Actually I'm working in Excel 2016
 
Upvote 0
Someone could help me with a formula to tabulate at least data for columns with green header?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top