Import of Text Delimited file into excell

[Bob1866]

Hi, I have set up a macro that imports data from a text delimited file using
the Text to Columns wizard and I have used the fixed width option which does
exactly what I need it to do except for the fact that the last column created
contains an number which virtually always changes in length, as it is the
last column I can make the column longer to accept the data but the problem
is that when a shorter number is used I get a comma at the end of the number
i.e. 147.50" . Is there something I can do with a macro or some formatting
that would remove the comma as I cannot do any calculations with the comma in
place.

I will not be able to manually delete the comma as the sheet will never be
open, it will just be running calculations in the background.

Any help will be much appreciated.

Thanks

Windows XP Professional
Office 2003

 

[Shane Devenshire]

Hi,

I'm not sure I follow - you say the file is delimited but you use fixed
with? Why not use the delimiter?

You say you get a comma at the end of the number but then you show us a
double quote" I don't know why that is happening but you show us data that is

147.50"

I see a period farther to the right but no comma?

You can strip the quotes from the last column output by running the Text to
Columns wizard on that column only and choosing delimited and entering the "
in the Other box as the Delimiter. 2. You can use a spreadsheet formula such
as
=--LEFT(H1,LEN(H1)-1)
where H1 is the cell with the qoutes after the data. You may have to change
the formula a little if this is not a double quote but two single quotes.