Formula Help

[OTPM]

Hi Forum Members


I am looking for a way of copying up to 5000 rows of data from Project to Excel without Excel using the comma as a numerical delimiter. Samples of data are as follows:
1157,1158,1159,1160,1161,1162,1163,1164,1165,1166, 1167,1168,1169,1170,1171
1530SS-18 wks,174
989SS,984

Any help would be appreciated.
Many thanks
Tony

[Catalin.B]

Hi Tony,
Why do you need a function, the "Text to columns" method is not a good option?
You can do it with VB, by splitting the content after "," then collect in the sheet all items separately... How the result must be, in a row, or in columns?
Maybe you can create a sample workbook with "before" and "after" data...

[macropod]

Catalin: Tony specified

without Excel using the comma as a numerical delimiter

Your post seems to assume the exact opposite.

Tony: I think you can get what you're after if you use Data|Get External Data > From Text. Navigate to and Selection the file, then click Import. When the Text Import Wizard pops up, choose Delimited > Next, then choose the Tab delimiter, making sure the other options are cleared. Click Finish then coose the destination.

[OTPM]

Hi Paul
Many thanks for your response.
The data I want to import is from a MS Project File. I can save the MS Project File as an Excel File (2003 format as 2010 does not work!). However I then have to copy and paste into my Excel Template.
Tony

[macropod]

Hi Tony,

I assume you can export the Project data to a text file, in the format you described in your first post. From there, you should be able to import the data as I described, using either Excel 2003 or 2010.

As it is, I don't have any problem copying & pasting the data from your post into Excel, without the commas being treated as delimiters.

[OTPM]

H Paul
Sorry . I missed the obvious in your last post. Saving the Project File as a CSV file and importing to Excel DOES work .
Sometime you are too close to see the obvious.
Thanks again.
Tony