formating Excel file

[webtekie]

Hello,

I need a way to format an Excel file while I am saving it. For example, if I have Excel Spreadsheet as:

ID Name State AreaCode
=== ==== ==== ========
1 Bob NY 212
2 Joe NJ 908

Once I would save this file as *csv I need it to look as follows:

1,'Bob','NY',212
2,'Joe','NJ',908

Another words I need to be able to mark columns as either Numeric or Text and save them accordingly .Is there anyway to accomplish this in Excel?

thanks,
webtekie

 

[SkipVought]

Hi,

Do as SaveAs and select CSV as the 'Save As Type'



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[webtekie]

yeah, but it saves as is without enclosing TEXT with "'"

 

[webtekie]

I mean without "''"

 

[SkipVought]

That's what a CVS does.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[webtekie]

So I can't export Excel data according to column type?

 

[SkipVought]

build a column using this formula,

copy the column

on a blank sheet Edit/Paste Special - Values

SaveAs unicode .txt



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[webtekie]

using which formula?

 

[bluedragon2]

="'"&B1&"'"



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

[tt]
=IF(ISNUMBER(A2),A2,"'"&A2&"'")&","&IF(ISNUMBER(B2),B2,"'"&B2&"'")&","&IF(ISNUMBER(C2),C2,"'"&C2&"'")&","&IF(ISNUMBER(D2),D2,"'"&D2&"'")
[/tt]
Couldn't you read my mind???



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[webtekie]

Sorry, I am not very good with Excel. I needed to convert data for SQL update on and did not want to spend time with regular expressions.
Now, is this a generic formula or does it only work for certain amount of columns? Also, (sorry if this is a trivial question) how do I use this formula in my Excel document?

thanks a lot,
webtekie

 

[SkipVought]

it works for the data example that you gave me -- ie 4 columns A - D

paste the formula in F1 and copy f1 down thru the rows of data.

copy column F and proceed the the remainder of the instructions above.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[webtekie]

Does this work for build data already? Because I did as you said, but once I pased F1 to A1 I just got first record formatted.

 

[webtekie]

sorry pasted...

 

[SkipVought]

paste the formula in F1 and copy F1 down thru the rows of data.

copy column F and proceed the the remainder of the instructions above. ie ...

on a blank sheet Edit/Paste Special - Values

SaveAs unicode .txt



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884