Unable to text-to-column

[spearhead]

Guys,

Need a big help. How can I text to column the following text file.

I need to only extract account number data. It a 16 digit no. I tried the text to column the way I know. but it does not give the info correctly.

Hopefully if a macro can be done for this, it would be great. coz i need to download this daily and try to do text to column.

[David A Coop]

I can step you through the Text to Columns approach.

1. Click on File, Open, Browse
2. Select the Text Files (*.prn;*.txt;*.csv) option next to File Name then choose the file - then Open.
3. You will see the Text Import Wizard and accept the recommendations. Click Next
4. Click Finish
5. Select Col A, then go to the Data Ribbon and click Filter.
6. Click on the Filter Arrow, and tick the Text Filters Box
7. In the drop down box, select Begins with and enter 0.

You will now see the 14 Account Numbers. (You will need to make col A wider to see it all!)

You can cut and paste these, and use them as you wish.

This took me less than 30 seconds.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[spearhead]

Thank you soooo much. You saved my time.

But how can I do this with a macro. Coz I will do this some times daily or weekly. So if done weekly, I need to do this 5 times. Coz there are 5 reports.

The report will have new data coming in daily, so the no. of rows that contains the data may vary. So how can i do the macro for this.

[spearhead]

will this do what I want.

Please Login or Register  to view this content.

[David A Coop]

Hi Spearhead,

This macro is not quite right.

Here is the code I came up with.

Please Login or Register  to view this content.

This code should be stored in a separate workbook. When you start it, you will be prompted to select the TEXT file you want to open.

It will then extract your data, and place it on a new sheet.

NOTE: The file you create will be a .TXT file - you will have to save it as .XLSX if you want to use it as an Excel file

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[spearhead]

I put the code in a work book and saved as xlsm.

But i get this error when running this.

Run-time error '1004':
Select method of Range class failed

[David A Coop]

Hi Spearhead,

First - the forum does not recommend using the Reply with quote button for the whole post - it just wastes space. People generally edit is so that only pertinent words are left.

Second - here is the workbook I created with the macro. I don't know what happened at your end!

Spearhead - Text to Col Extract.xlsm

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[MarvinP]

Hi Spearhead,

This is a perfect problem for Power Query. You have in your newer version of excel. Power Query pre-formats different kinds of files before they end up in Excel. Start learning Power Query. Perhaps at:

https://support.office.com/en-us/art...5-89F6269CD605

The Power Query can be saved to do the exact same import with your files. Time to start learning Power Query.?

[mike7952]

Another way... And Im going to check out Power Query. Thanks Marvin P

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[spearhead]

Thanks david. Your a great helper and a genius.

IS there a way to use this to extract several text files at the same time. Coz we download a single file for each day at the end of the month, then text to column it one by one. Can this be changed to select and do it for all the files at the same time.

[David A Coop]

Hi Spearhead,

Thanks for your compliment, but I am far from a genius. Just look at @mike7952 and @jindon above - far more elegant because they bring the data into the workbook itself.

This code will open multiple - I pinched @mike7952 code because it is neater than mine.

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[jindon]

IS there a way to use this to extract several text files at the same time.

Please Login or Register  to view this content.

[spearhead]

David, Mike and Jindon. I don't know how you guys do it but the help you guys are giving is totally appreciable. Thank you so much.

Sorry to ask this again,
Is it possible to extract the date in the report in column A and in Column B the account no. after converting. Rather than having the file name as seperaters in the converted file. And also to ignore files with no account no. Data. Coz we found that there was a file for 28.11.2015, which had no account no. therefore it displayed an error Run time error : 1004 application defined or object defined error.

[David A Coop]

Sorry,

I don't understand your request.

Please provide an example of how you now want the file to appear, and an example file which causes the error.

DAC

[spearhead]

I dont have a text file with me now, but will attach a sample excel file. My request is this,

1) There is a date in the text file, after converting can you make the final report to display the Date in column A and the converted 16 digit number in the column B. This is since we are converting multiple files.
And can you adjust the code to not display the text file name (highlighted in yellow color)

2) There are some text files which does not have the 16 digit no. Therefore, can the code be set to ignore these kind of files if it cant find any 16 digit numbers in the file. Currently when processing such text files, the above error is given.

[spearhead]

Anyone can help me with this.

[David A Coop]

Sorry Spearhead,

I hoped one of the experts could help as my VBA skills are not good.

This is what I came up with:

Please Login or Register  to view this content.

I hope this helps.

Please let me know.

David

[spearhead]

Hi David, you are more than a expert. Coz you helped me all this time.
Will you be able to add this code and send the file as version 3. Coz I don't know where to add this, since in the version 2 of your file there are 4 macros.

[David A Coop]

Hi David, you are more than a expert. Coz you helped me all this time.
Will you be able to add this code and send the file as version 3. Coz I don't know where to add this, since in the version 2 of your file there are 4 macros.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[spearhead]

I'm getting a Runtime error 9. Subscript out of range.

[David A Coop]

I'm getting a Runtime error 9. Subscript out of range.

I don't! These are the 3 files I used to test.

Report (1).txt
Report (2).txt
Report (3).txt

I would have to see your files that cause the error.
David

[spearhead]

I don't! These are the 3 files I used to test.

Attachment 436124
Attachment 436125
Attachment 436126

I would have to see your files that cause the error.
David

David, Thank you so much for all the help you have given me. I changed the ReDim my data to 1-20000. Now it workds.


Once again I highly appreciate the help you have given me and its nice to know that there are ppl in this world that help other ppl eventhough they dont know that person.

[David A Coop]

Good thinking Spearhead.

Thanks for the encouraging comments. It gives me much pleasure when I can help someone in this way.

Kind regards,

David

[David A Coop]

Good thinking Spearhead.

Thanks for the encouraging comments. It gives me much pleasure when I can help someone in this way.

Kind regards,

David

By the way, don't forget to close the thread by marking your original post as SOLVED. (See below)



- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[spearhead]

Thanks David.

One final help from you, will you be able to set the date column to format as "dd/mm/yy". Since it is extracted and displayed as text.

Sorry for the trouble again.