Excel forces imorted text into formula

[micro-blades]

I have a text file I am importing and using text-to-colums.

Some of the text is "+TOL" "-TOL" and when excel converts to columns it is forcing the text to a formula "=+Tol" "=-Tol" giving me #NAME? in those cells.

I can format the cells as text, which is fine for now since I'm using VBA, but if I need to use any formulas then I'm SOL...

Any ideas on how to prevent this?

Thanks!
Brian

 

[Makrini]

Use the text to columns to force text...

I don't understand the problem with changing to text as you couldn't use a calculation on +Tol anyhow.

 

[Richard Schollar]

Just format the cells you want to use as formulas in General format before you insert the formula.

 

[micro-blades]

I can't run a calculation on +Tol, but there are many formulas I could run on the text. Plus, there are many values I am after that I may want to use formulas on rather than code.

Using text to columns to force text gives the same result, excel still forces a formula.

I have set the entire sheet's format to text before importing and that works except, then I can't enter a formula into any cells. I can change the individual cell's format back to general prior to entering the formula but would rather not have to.

I can 'work around' what's happening with code but I was hoping there was a simpler solution.

Thanks,
Brian

P.S. Thanks Richard, must have posted at the same time.

 

[micro-blades]

Scratch that, formatting the entire sheet to text does not change excels actions either.

Text to columns appears to override any settings I choose.

If I select the cell afterward and delete the "=" that excel added, it works.

But still, how can I prevent excel from forcing the formula?


Here's some of the text, paste it into a sheet and use text to columns to see what I mean:

DIM DIST_VFR_3= 3D DISTANCE FROM POINT PNT_CHK_3 TO POINT PNT_VFR_3, NO_RADIUS UNITS=IN
AX NOMINAL +TOL -TOL MEAS MAX MIN DEV OUTTOL
M 0.0000 0.0010 0.0010 0.0000 0.0000 0.0000 0.0000 0.0000 -#-

Thanks,
Brian

 

[Makrini]

If I do text to columns on that..

I get +tol as a formula

If I do text to columns - choosing text as column format (step 3 of the wizard) - I get +tol as text

 

[micro-blades]

If I do text to columns on that..

I get +tol as a formula

If I do text to columns - choosing text as column format (step 3 of the wizard) - I get +tol as text

Well that's frustrating...I tried again, in a new workbook using only the text above, getting the same results I was previously.

I'm also selecting delimited, and "space" as the delimiter, but either delimited or fixed width doesn't seem to change my result.

I don't know what else I'm doing different.

I appreciate everybody's help with my petty issue!!

Brian

 

[Richard Schollar]

How are you getting the data into Excel? Are you using an Open method in VBA?

 

[micro-blades]

Are you using an Open method in VBA?

No, I'm just pasting it into A1. I mistated in my initial post that I am "importing a text file" sorry for that.

I just did some more intense looking at the text to columns and didn't realize you had to set the text property for each column by selecting the column from the dialog and then selecting text. I assumed selecting text once effected all the data.

That solves the issue for the most part. I would just rather that excel didn't force the formula in the first place. If the text started with "=" I would understand, but changing "+Tol" to "=+Tol" I just don't get.

Thanks for your time! I know it's precious!

Brian

 

[Richard Schollar]

No worries Brian - I'm glad you got it sorted

Takes time to learn the ins and outs of the various bits of Excel (and then you go and upgrade to 2007 and have to relearn the lot!)...