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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
194
admvlad
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
83
Peter_SSs
Peter_SSs
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
566
Alex Blakenburg
A
D
  • Solved
Groupby include only counta < 4
Replies
5
Views
211
Alex Blakenburg
A
tabbytomo
  • Question Question
=IF Exact formula stops working part way through
Replies
1
Views
249
arthurbr
arthurbr

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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