Psuedo Formulae in Excel

[webrabbit]

In my application, I export text and other data to Excel. Occasionally, some text strings start with a "+". Excel treates these as malformed formulae. How do I make Excel treat this data a simply text?

 

[SkipVought]

Hi,

"...I export text and other data to Excel..."

How are you exporting?

How are these cells FORMATTED? If they are formatted as TEXT, you should not have this problem.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

I write them to a .CSV file, which I then issue a START ....

 

[SkipVought]

"...which I then issue a START ..."

Rather than OPEN the .csv, IMPORT into an Excel worbook sheet, via Data > Import External Data > IMPORT...

In the import wizard, you can specify columns to be imported as TEXT.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

The process is automated via a .BAT file. How can I do an Import via a .BAT file?

 

[SkipVought]

You cannot because a bat file runs external to Excel.

What is the point of a bat file?

Please explain the process, and the business case.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

The batch file runs tha application, which generates a report as a CSV. Then the batch file checks to see if the CSV file has been created, and if so, opens it with the START command.

 

[SkipVought]

Hence, your problem.

So it opens in Excel.

Note the File/Path.

CLOSE the file - its a throwaway.

Create a NEW workbook.

THEN import the csv file as outlned above, using the File/Path.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

You could also, in the offending workbook, select the column with the #NAME errors and use Edit > Replace, replacing the EQUAL with NOTHING.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

It is not I but the customer who runs my application. I just want the report to pop up in Excel.

 

[SkipVought]

Record a macro that the user runs in Excel, that either IMPORTS or OPENS the csv.

If IMPORT the macro can parse correctly.

If OPEN the macro can Edit > Replace the EQUAL characters.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

1. How does the macro start?

2. The text string does not have an "=" character, just a "+" character follwed by letters, digits and other characters.

 

[SkipVought]

"...The text string does not have an "=" character..."

You originally stated, "Excel treates these as malformed formulae."

Check what's REALLY in the cell, in the Formual Bar, and then tell me.

"How does the macro start?"

In the Workbook_Open event. If you want to pursue this option, please post in Forum707



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webrabbit]

What I have contol over is what goes into the .CSV file. PERRIOD.

 

[webrabbit]

My son called me. I asked him the question. He said put an "=" before the open quote. That does exactly what I want, causes Excel to treat the string within the quotes as text, nothing else.

Case closed.

 

[SkipVought]

"...before the open quote..."

Where was there ANY mention of an OPEN QUOTE???

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[GlennUK]

Yes Skip, what an example of failing to tell us all the of facts. Am glad I wasn't the one putting the effort in trying to help webrabbit.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[webrabbit]

As I said, I can put any codes in the .CSV. I tried using quotes, it didn't make any difference, except when the text itself contained commas. Excel extracts the string from the quotes and then interprets it. Numeric strings are treated as numbers, and strings that start with a "+" are treated as mal-formed formulae. I can prevent the former by including a space before the close quote, but I couldn't come up with any way to stop the latter. The "=" is a general solution.

Being able to put any code in the CSV is implicit in my original statment that my application generates the CSV.

 

[SkipVought]

Glad you got an answer that works for you.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

Glenn,

Sometimes it's like pulling teeth; agonizing in the process of getting to the root, and does not want to budge.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}