How extract data of different width from Text File to Excel using VBA ?

[drdang]

Hi All,

I am looking to import data in text file to Excel sheet. Here is the main catch - All the data are not of fixed length and are in multiple lines
as in the example below.

Text File:
ID001 Dr Dang 01012011 Male
USA Married 123

Below is how I expected the data extraction in Excel:
ColumnA ColumnB
CardID ID001
Name Dr Dang
DOB 01012011
Sex Male
Country USA
Status Married
Pin 123

Please help !!!!

 

[hiker95]

drdang,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[drdang]

Hi hiker95
Im using Excel 2010 and Windows 7.

I have uploaded the sample text file and excel at this LINK

I am looking for data extraction based on relative positioning. Like first data of the first row in text file should be extracted from 1st position till 5th position. Please note that first data of the second row in the text file starts from 1st position till 3rd position.

Let me know if more information required.

Thanks,
DrDang

 

[hiker95]

drdang,

One record/example is not enough.

We do not need the text file.

Can we have another workbook containing all the data from the text file?