How to Delete White Space while writing from EXCEL to Text File?

[Sharat Chandrasekhar]

The subject line asks it all:

I have text data in an Excel file that I want to write to a plain
text file from within a vba macro.

I want to delete ALL blank spaces (leading, intermediate and trailing)
in each text string before writing it to the plain text file.

Any ideas on how to achieve this? Please note that I do NOT want to
eliminate white space from the data in the EXCEL file itself.

Thanks in advance

-Sharat

P.S: On a more general note, is it actually possible to format data in
vba prior to writing it to a plain text file?

 

[Dave Peterson]

Not sure how you're writing your data, but you can remove all the spaces from a
string by using Replace (xl2k or higher) or application.substitute (xl97).

Dim myStr as string
myStr = "this is a test"
mystr = application.substitute(mystr," ","")
or
mystr = Replace(mystr," ","")

And yes, you can format numbers before you write them:

dim myNumber as Double
mynumber = 1243.3234
msgbox format(mynumber,"#,00.00")

 

[Sharat Chandrasekhar]

Dave, Thank you very much for your help. The replace command worked
fine!

Regarding

dim myNumber as Double
mynumber = 1243.3234
msgbox format(mynumber,"#,00.00")

however, I was actually trying to write the formatted cell contents to
a plain text (ASCII) file, not a messagebox. I couldnt find any
arguments in conjunction with the print and write commands in vba that
would let me do that.

-Sharat