Formatting data coming from external source into excel

I'm pulling in data from an external source into excel PT and part of the information coming in that I need is in a text format but I need it to be as a number so that I can use it to create a calculated field. What would be the best way to be able to convert that field to a number when the data gets dumped into the PT so that I can create the calculated field I need?

also posted this at:
http://www.mrexcel.com/forum/excel-q...ml#post4503813

Originally Posted by FDibbins

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

My second post which was 30 seconds after the original post shows a link to the post on another forum. I'm not sure what you're talking about when I clearly posted the link to the other thread which is what the rule says to do.

Originally Posted by ben_hensel

"It depends."

If the cell is a number written out as text (eg, "eighty-four" instead of "84") then you will need another layer of processing (function or macro) to translate.

If it's a number formatted as text that's imported, then you can select the column and use Text-to-Columns to convert it to a number format.

If it's an external data connection (you're feeding data into the PT without going through a table on a spreadsheet) then you're out of luck -- but you can process incoming data if you go to a Power Pivot instead.

It was the 3rd option I was trying to do where 84 is formatted as a text field instead of a number but still shows as 84.

Thanks...just wanted to see if I could do it that way but it sounds like I need to external data connection going directly into power pivot instead to allow this to work.