Text file to excel

[sroot]

i have a text file that i would like to export or copy and paste into excel, but the problem is getting it formatted into the correct cells.

here is an example of what i have in a text file....

123456 John A. Doe Supplies - Card ARG3452 2014-07-29 110.83-
123456 P9 DTVC Spend Mgmt - SR1M ARGD394 2014-07-29 1190.37

This is how i would like it to look in excel, each in its on column...

123456 John A. Doe Supplies - Card ARG3452 2014-07-29 110.83-
123456 P9 DTVC Spend Mgmt - SR1M ARGD394 2014-07-29 1190.37

Any ideas on if i can use a formula or a macro?

[shg]

How would Excel know where to divide the columns?

[sroot]

well i could even make it work if i had the 123456 in one column, all the stuff till the date in another, the date in another, and the dollars in the last one...

[shg]

How are you importing it now?

[sroot]

It is being done manually right now. But it is taking over an hour and i am sure there is an easier way to do it.

[shg]

I don't know what that means, "manually." Are you copying and pasting from NotePad?

If so, you should instead OPEN the file from Excel, then you can use the text import wizard.

[sroot]

Yes i am copying and pasting from notepad. That's the problem, I cant import it that way because the information before the dates can have one or more words in it so there is no way to separate it to make all the dates and dollar values in the same column.

[newdoverman]

Perhaps something like this would be appropriate:

	A	B	C	D	E
1	Number	Name	Description	Date	Amount
2	123456	John A. Doe	Supplies - Card ARG3452	29/07/2014	110.83
3	123456	P9 DTVC Spend Mgm	SR1M ARGD394	29/07/2014	1190.37
4

To do this, commas or tabs would have to be inserted into the correct places (including the headers). The data would look like this:

Number,Name,Description,Date,Amount
123456,John A. Doe, Supplies - Card ARG3452,29/07/2014,110.83
123456,P9 DTVC Spend Mgm, SR1M ARGD394,29/07/2014,1190.37

I took a guess at what belonged in each column.

[sroot]

That is how i would like it to look, the problem is getting the commas in there since there can be thousands of records

[newdoverman]

I copied your sample into Word and used the REPLACE feature then copied the results back into Excel. These are the exact steps that I followed in Word to separate the text into columns.

Click on Replace and then More and select Use Wildcards.
1/. Find ([0-9]{4}) ([A-Z]) replace with \1^t\2

2/. Find space-space (spacebar in place of word space) Replace with ^t

3/. Find ([0-9]{4}-[0-9]{2}-[0-9]{2}) replace with ^t\1^t

4/. Find ^l (^ and lowercase L) replace with ^p

5/. Find -^13 replace with ^p


This is the result in Excel with only the column width changed to accommodate the data:

	A	B	C	D	E
1	123456	John A. Doe Supplies	Card ARG3452	29/07/2014	110.83
2	123456	P9 DTVC Spend Mgmt	SR1M ARGD394	29/07/2014	1190.37