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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
253
admvlad
A
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
599
Alex Blakenburg
A
D
  • Solved
Groupby include only counta < 4
Replies
5
Views
214
Alex Blakenburg
A

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

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