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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
You must log in or register to reply here.

Similar threads

T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
288
Tarkemelion
T
MartinS
  • Question Question
Update FormatConditions Shifts Column/Row Reference
Replies
0
Views
289
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
607
Domenic
D
JenniferMurphy
  • Question Question
Is this the best way to calculate the number of events since the last one?
2 3
Replies
25
Views
686
Alex Blakenburg
A
J
  • Solved
Forumla Error on Sum of lines Containing same Value
Replies
2
Views
345
jarett
J

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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