NEED HELP IN GETTING TEXT FROM .txt to Excel Worksheet

A

arthurps

New Member
Joined
Apr 6, 2013
Messages
3
Dear Friends,

Bellow is the format of text file, which I need to extract to excel sheet sorting on first ID Value

000080CC | IP:172.16.16.52 | 03-04-2013 09:16:54
000080CC | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5DD | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5D7 | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5DD | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5D7 | IP:172.16.16.52 | 03-04-2013 09:16:54
000080F0 | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5DD | IP:172.16.16.52 | 03-04-2013 09:16:54
0000A5D7 | IP:172.16.16.52 | 03-04-2013 09:16:54
000080F0 | IP:172.16.16.52 | 03-04-2013 09:16:54
000080CC | IP:172.16.16.53 | 03-04-2013 09:16:54
000080CC | IP:172.16.16.53 | 03-04-2013 09:16:54
0000A5DD | IP:172.16.16.53 | 03-04-2013 09:16:54
0000A5D7 | IP:172.16.16.53 | 03-04-2013 09:16:54

In the excel sheet, I need something like this bellow, The data should be sorted on Local ID,

Column 1 Column 2 Column 3
Local ID IP Date and Time
0000A5D7 172.16.16.53 03-04-2013 09:16:54

Hope to receive the help at the earliest.

Thanking you,
Arthur.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Where is the text at the moment? And what format is it in. Is the top line all one big character string? from 00008 through to 16:54
 
Upvote 0
000080CC172.16.16.5203-04-201309:16:54
000080CC172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
000080CC172.16.16.5303-04-201309:16:54
000080CC172.16.16.5303-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54
0000A5D7172.16.16.5303-04-201309:16:54

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

It is one long text string I replaced the | with blanks and then used left, mid and right fuctions - the first column is left (a1, 8)
 
Upvote 0
000080CC172.16.16.5203-04-201309:16:54
000080CC172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
000080CC172.16.16.5303-04-201309:16:54
000080CC172.16.16.5303-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54
0000A5D7172.16.16.5303-04-201309:16:54

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

now sorted with column 1 ascending
 
Upvote 0
000080CC172.16.16.5203-04-201309:16:54
000080CC172.16.16.5203-04-201309:16:54
000080CC172.16.16.5203-04-201309:16:54
000080CC172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
000080F0172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5D7172.16.16.5203-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54
0000A5DD172.16.16.5303-04-201309:16:54

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Dear oldbrewer,

I appreciate your help, I have a long long file in which I have around 4000 entries in each file, it is generated by RFID reader, I need to make a proper report out of this file. I need a an small macro or vba program to be executed and like the above I need the out put in the excel file.

Thanking you,
Arthur.
 
Upvote 0
I do not know what RFID reader is. You need to export your data to excel first and then make a table like I did to process the data into its correct components. This is fully automated. you can then analyse your data and produce a management report. Is your original data table exactly as it will appear in Excel? Or can some components vary in number of characters long?
 
Upvote 0
Yes, The original data is in text file and it appears exactly how I have given above, You may see a single card is been read several times from the RFID reader. if at all I get the data has you have given in single column like

Column 1 Column 2 Column 3
Local ID IP Date and Time
0000A5D7 172.16.16.53 03-04-2013 09:16:54

Then I could sort and remove the unwanted and prepare proper report.

I just need a way to send the systemic data from the text file to excel column.

Thanking you,
Arthur.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Calculate the average of a column with time excluding rows with values in other columns
Replies
4
Views
495
arthurbr
arthurbr
L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
959
Armstrong_N14
A
D
  • Solved
Need help with formula (Match/Index?)
Replies
3
Views
421
Fluff
Fluff
J
  • Question
VBA to Create Table and to Remove Duplicates
Replies
0
Views
646
jessebh2003
J
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
508
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,540
Messages
6,120,106
Members
448,945
Latest member
Vmanchoppy

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