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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54
[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
</tbody>[/TABLE]

now sorted with column 1 ascending
 
Upvote 0
[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080CC[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]000080F0[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5D7[/TD]
[TD]172.16.16.52[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
[TR]
[TD]0000A5DD[/TD]
[TD]172.16.16.53[/TD]
[TD]03-04-2013[/TD]
[TD]09:16:54[/TD]
[/TR]
</tbody>[/TABLE]
 
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 Question
Calculate the average of a column with time excluding rows with values in other columns
Replies
4
Views
660
arthurbr
arthurbr
L
  • Question Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
1K
Armstrong_N14
A
D
  • Solved
Need help with formula (Match/Index?)
Replies
3
Views
547
Fluff
Fluff
J
  • Question Question
VBA to Create Table and to Remove Duplicates
Replies
0
Views
1K
jessebh2003
J
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
758
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,234
Messages
6,170,891
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