convert excel to text file with no tabs

[naomi]

Hi,
I need to convert an excel file to a text file which is not tab delimited.
Does anyone know how to do this? If I "save as" a text file, whatever option
I choose, there are tabs between the cells.

 

[David Biddulph]

Other text options are space delimited (.prn) or comma separated (.csv).

 

[naomi]

Thanks - but it still isn't what i need. It puts 3 or 4 spaces between the
contents of each cell. I need a single space. Apparently it can be done,
but no-one sems to know how.

 

[Gord Dibben]

It can be done.........many know how and very few have a problem.

In which application are you opening the *.csv file?

NotePad or some other text editor?

What is the cell formatting of your original data in the worksheet?


Gord Dibben MS Excel MVP

 

[Dave Peterson]

And if a cell contains spaces, should it be surrounded by doublequotes?

You may be able to get more help by describing your requirements.

Or maybe you could try building a formula in an unused column:
=a1&" "&b1&" "&c1&" "&d1
and drag down as far as you need.

Then copy that column and paste into Notepad and save the text file from there.

 

[naomi]

Unfortunately I am one of the few who have a problem.
The csv file is close to what I need, but I need a space instead of a comma.
I am opening it with NotePad.

I am trying to generate a file to upload to a website for processing and
they have very strict requirements for the layout of the file. I cannot
enter it straight into NotePad as it is 500 records.

The cell layout is as text (even though the data is mostly numbers, but
Excel does funny things with the dates if I do it any other way - I need
04/10 etc).
There are no spaces within the cells.

 

[Dave Peterson]

You could try this:

Change your windows regional settings (just temporarily).
In windows XP, open control panel
Regional and language options|Regional Options tab
click the customize button
On the Numbers tab, change the list separator to a space character.

Then save the file as a .csv file.

(And then change the regional settings back to what you used before (comma or
semicolon???).

=========
Or you could use a formula that does all the formatting and concatenation:

=a1&" "&b1&" "&c1&" "&text(d1,"mm/dd")&" "&text(e1,"$0,000.00")

 

[naomi]

Thank you. The regional settings thing worked

You could try this:

Change your windows regional settings (just temporarily).
In windows XP, open control panel
Regional and language options|Regional Options tab
click the customize button
On the Numbers tab, change the list separator to a space character.

Then save the file as a .csv file.

(And then change the regional settings back to what you used before (comma or
semicolon???).

=========
Or you could use a formula that does all the formatting and concatenation:

=a1&" "&b1&" "&c1&" "&text(d1,"mm/dd")&" "&text(e1,"$0,000.00")