Retrieving the characters lost

[Alogon]

Hello everybody.

When I convert a txtfile into xlsfile, a cell that must contain the text "-
John" (ignore quotation marks) become a formula "=- John" (ignore quotation
marks). In the worksheet, Excel displays the value "#Name?" (ignore quotation
marks) at that cell and displays "=- John" (ignore quotation marks) at the
edition bar.

Using VBA, how can I get back the original characters "- John" ?

Thank you in advance.

 

[paulgu]

There's no need to use VBA. Just do a find and replace, finding =- and replacing with '- (that's a single quote). Make sure that Look In in the Find & Replace dialog has formulas selected.
HTH
Paul

 

[OssieMac]

When you say you are converting a text file to an xl file do you mean that
you are importing the text file. If so, what type of text file? Is it a .csv
file or a .txt file?

Now you say you want the VBA code but first:-

If it is a .txt file then in the Text to columns dialog box you can select
the format for specific column and set it as Text and that will probably
overcome the problem.

If it is a .csv file then the dialog box seems to get bypassed and you don't
get the opportunity to set the format for the column. I usually get around
this by changing the file name from .csv to .txt in windows explorer. (Need
to set your explorer options to display the file extension.)

Now for the VBA. You should be able to get the correct VBA syntax for the
Text to columns by recording the operation.

Hope this helps.

 

[Alogon]

Just on target about .txt file, OssieMac.

Very opportune your mention about the .CSV file too.

Thank you very much.

Regards from Brazil.

Nathan