Text to Data or Columns

J

Jhong

New Member
Joined
Aug 15, 2017
Messages
7
I basically get my Data from external dashboard through Copy then paste matching source as Get data from Web function is not possible due to IE limitation. the Data however, when I paste to excel would only be on Column A, that is fine but I need to process it just like in my Example Column B onwards. Instead of using delimiter option redundantly I need a formula to get values as that of Row 2,3,5,9,10,13,14,17,18 of Column B onwards as sports available could vary to include any sport being played.

Thanks

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Section A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66"]Section A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66"]33 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="class: xl66"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]33 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Tennis[/TD]
[TD]Football[/TD]
[TD]Rugby union[/TD]
[TD]Badminton[/TD]
[TD]Basketball[/TD]
[TD]Volleyball[/TD]
[TD]and soon...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Tennis(23) - Football(4) - Rugby union(1) - Badminton(1) - Basketball(1) - Volleyball(3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]23[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Section B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Section B[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66"]0 Event[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="class: xl66"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]0 Event[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Section C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Section C[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66"]33 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Last updated at 16:52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]33 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Tennis[/TD]
[TD]Football[/TD]
[TD]Rugby union[/TD]
[TD]Badminton[/TD]
[TD]Basketball[/TD]
[TD]Volleyball[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Tennis(23) - Football(4) - Rugby union(1) - Badminton(1) - Basketball(1) - Volleyball(3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]23[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Section D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Section D[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66"]5 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="class: xl66"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]5 Events[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Football[/TD]
[TD]Rugby union[/TD]
[TD]Basketball[/TD]
[TD]Volleyball[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Football(1) - Rugby union(1) - Basketball(1) - Volleyball(2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Section E[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Section E[/TD]
[TD]1 Event[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="class: xl66"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]1 Event[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Football[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Football(1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl65"]Last updated at 16:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
330
Tarkemelion
T
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC
E
  • Solved
All Unique Combinations from 5 Columns of Data + Customizations - Excel 365
Replies
6
Views
241
Excel Jason
E
MartinS
  • Question Question
Update FormatConditions Shifts Column/Row Reference
Replies
0
Views
294
MartinS
MartinS
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
675
Domenic
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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