text to column or mid/find nesting

Status
Not open for further replies.
D

Deinoc

New Member
Joined
Nov 23, 2017
Messages
6
So I have this problem when importing a csv file, one cell has information that needs to be split into columns or repeated with the same tablet id. I have tried several combinations but everything takes time and it is not clean and it doesnt solve the problem, any ideas data is below and the two outputs are what I am trying to split, thanks!:



[TABLE="width: 868"]
<tbody>[TR]
[TD]RAW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk (3)[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (23)|Fairgrounds (1)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)|Fairgrounds (3)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OUTPUT 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]Amount[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OUTPUT 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Elk[/TD]
[TD]Rosemont[/TD]
[TD]Main[/TD]
[TD]Fairgrounds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Deinoc said:
So I have this problem when importing a csv file, one cell has information that needs to be split into columns or repeated with the same tablet id. I have tried several combinations but everything takes time and it is not clean and it doesnt solve the problem, any ideas data is below and the two outputs are what I am trying to split, thanks!:


[TABLE="width: 868"]
<tbody>[TR]
[TD]RAW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk (3)[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (23)|Fairgrounds (1)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Elk (1)|Rosemont (4)|Main (15)|Fairgrounds (3)[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OUTPUT 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tablet ID[/TD]
[TD]Placement[/TD]
[TD]s_det[/TD]
[TD]Amount[/TD]
[TD]notes[/TD]
[TD]language[/TD]
[TD]date_added[/TD]
[TD]last_update_date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/15/2017 19:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: right"]11/15/2017 18:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Elk[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Rosemont[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Main[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]Fairgrounds[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: right"]11/16/2017 20:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OUTPUT 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Elk[/TD]
[TD]Rosemont[/TD]
[TD]Main[/TD]
[TD]Fairgrounds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:52[/TD]
[TD="align: center"]#########[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD]English[/TD]
[TD="align: right"]11/15/2017 18:55[/TD]
[TD="align: center"]#########[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Can you post your sample CSV file?
 
Upvote 0
Status
Not open for further replies.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
168
admvlad
A
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
530
Alex Blakenburg
A
D
  • Solved
Groupby include only counta < 4
Replies
5
Views
198
Alex Blakenburg
A
tabbytomo
  • Question Question
=IF Exact formula stops working part way through
Replies
1
Views
237
arthurbr
arthurbr
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
114
Fluff
Fluff

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

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