Excel Web Query Formatting?

[timlash]

I'm importing data via Web Query. A large group of individual cells are each getting populated with two separate negative numbers, like this: " -5 -110"

When importing the data Excel assumes this is a mathematical operation and enters the data as: = -5 -110

Thus I get -115 as the resulting value in the cell when I'm really interested in the two different numbers.

1) Can I force Excel to treat this incoming Web Query data as text so that I can parse these values?

2) Is there a way to reference parts of a formula in a cell?

I've tried to formatting the cells prior to and post data download. I've also tried altering formula evaluation settings as well as different Web Query options to prevent Excel from assuming I want an arithmetical operation.

I created an illustrative report and example here:

http://excelhelp.wikia.com/wiki/Excel_Web_Query

Using Excel 2002. Any advice would be appreciated.

 

[John_w]

After retrieving the data precede the cell value with an apostrophe to make Excel treat it as text. For your Wiki example:

Range("C119").Value = "'" & Mid(Range("C119").Formula, 2)

 

[timlash]

John,

Thanks for the VBA approach. Unfortunately, a VBA solution is less than ideal since I'll have hundreds or thousands of instances like this to address and the location of their occurrence will change periodically. Sometimes a particular leading figure will be negative and require adjustment, but other times that same occurrence will be positive and Excel will behave as desired.

I really need a way to tell Web Query to treat all incoming data as text.

Thanks,

Tim