Convert $ to actual numbers in an imported web query

I use the Web Query function to import tables with data from the internet to 
my spreadsheet. However, some of the amounts imported are in $ format (e.g. 
"$7,950.00") and do not function as numbers, i.e. I cannot perform 
mathematical calculations on them. How do I import $$ values as actual 
numbers?

Thanks for your help.
0
Utf
12/13/2009 2:41:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
676 Views

Similar Articles

[PageSpeed] 34

      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Alex" <Alex@discussions.microsoft.com> wrote in message 
news:4F1792D6-849C-4E99-BC9A-C78E3E09F8D2@microsoft.com...
>I use the Web Query function to import tables with data from the internet 
>to
> my spreadsheet. However, some of the amounts imported are in $ format 
> (e.g.
> "$7,950.00") and do not function as numbers, i.e. I cannot perform
> mathematical calculations on them. How do I import $$ values as actual
> numbers?
>
> Thanks for your help. 

0
Don
12/13/2009 3:14:44 PM
Reply:

Similar Artilces:

Converting to PDF #2
I lose my hyperlinks to email addresses when I convert from Publisher 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Do I need to upgrade to Publisher 2003 or can this be fixed? While in a state of ecstasy after repairing his laptop, Ed sees a message from Banks <Banks@discussions.microsoft.com>. On it is written: > I lose my hyperlinks to email addresses when I convert from Publisher > 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Publisher sends printable information to the printer driver. As you cannot click a hyperlink on a printed piece of paper, Publisher doesn&#...

Add Invoice Number to Report
I have created a delivery table that contains all of the usual fields , Pickup, Delivery, Billing, NoPeices, Weight... etc. The table is updated with current delivery information daily. At the end of the week I create a report grouped on Billing customer that prints an invoice with all of the unprinted deliveries the customer is paying for... the report works fine. What I need to figure out is how to assign a unique invoice number (autonumber) to the report that will be associated with each Billing customer for that print period. Can I somehow generate a unique number at the time I ...

Add SafePay footer record for date and account number
Union Bank of California has a Positive Pay format that requests a footer record for each day and account number. So if you transmit checks issued on two dates for a single account, the SafePay file would have two footer records--one for each date. Currently, I am only able to create a footer by account, totalling all checks issued for that account (regardless of date), and attributing that total to the Issue Date in the footer record. Union Bank reads the issue date on the footer, and sees that the checks issued on that date do not match the footer total, causing them to consider th...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

Taste the important pack from the M$
--ijcjxmqheu Content-Type: multipart/related; boundary="jehqgmhvtnsz"; type="multipart/alternative" --jehqgmhvtnsz Content-Type: multipart/alternative; boundary="kgjgjtzi" --kgjgjtzi Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to continue keeping your computer secu...

assign numeric value to letters and sum with other numbers
I apologize if I am duplicating an earlier question, but I can't find the answer. How do I sum a row or column that has numbers and letters by giving the letters a numerical equivalent? -- WJG On Mon, 11 Jan 2010 12:19:01 -0800, Galadad <Galadad@discussions.microsoft.com> wrote: >I apologize if I am duplicating an earlier question, but I can't find the >answer. How do I sum a row or column that has numbers and letters by giving >the letters a numerical equivalent? Could you give an example of input and expected output. Lars-�ke Just guessing...

periods not importing
I have tried several ways to import periods in the Title, Suffix, and Middle Name fields, and I am getting box characters, rather than the period. I am using OL07. I have even tried replacing all periods with semicolons, the letters "qwer", and the letters "aa". And still, when it gets to OL, it shows up as unprintable box characters. What's going on? I would use Mapilab to replace these things, if I even knew what to call them. thx for any help. -- Boris "BorisS" <BorisS@discussions.microsoft.com> wrote in message news:E54...

Free Data Process/Query Tool
If you use Excel everyday and spend lots of time on data/text processing, then you will find the this tool (UDQ Add-in) is very powerful and can speed up your daily data processing work. Currently the tool is free for everyone. Please feel free to distribute this tool to your friends/colleagues if you think it is helpful to your work. The following is a list of features/functionality of the tool: 1> Query/Import Data from Multiple External Data/Text/ASCII Files (Can useful import data from hundreds of files within minutes) 2> Query/Import Data from Multiple Closed Excel Files (Can...

Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....

disabling queries or dataranges
I have a large spreadsheet with many embedded queries pulling from an access database that i want to publish to lots of different people. how do i keep the queries from trying to refresh when those people open the book, besides cutting and pasting all the values from one spreadsheet into another one without data ranges? I want the spreadsheet to ask me whether it should refresh on opening, and it does, but i want to be able to disable this and send it on without lot's of work. excel 2000. thanks. -- johnlengacher -------------------------------------------------------------------...

query will not write to excel
I have set up a query to a Foxpro .dbf file in a database from excel. When I tell Excel to import the data it it appears to be working but never seems to return the data. Running the same query via msquery.exe returns the data with no problem. Can anyone tell me what the problem is ? ...

Web services stop after rebooting server
Every month when I go to patch my Windows 2000 Server I have issues with the WWW service stopping every 5-10 minutes. Eventually after a day or so this goes away and all is well. This has happened since we went to v3. Any thoughts? EventType : crmmanaged P1 : 3.0.5300.0 P2 : aspnet_wp P3 : microsoft.crm P4 : there is an error in xml document (1_ 970).;hash='-493497873' ...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

Converting Numerical values to Words
I am fairly new to the ins and outs of Microsoft Access 2003 but have been able to work through all of my problems thus far except one. I started using check boxes for storing user inputed data from a form. When the box is checked I have made it equal a value ranging from 1 to 6 according to the desired group. This stores the number in a table which I then reference many times in queries as well as reports. Here is my question, how do I convert from those stored numbers, taken from the check boxes, to words that can be easily outputted to a report so that anyone can read it without ...

Add phone number to quote report.
Hey guys, My retail staff asked me if it is possible to add the customer phone number to the Quote summary or Quote detailed report. Does anyone know what changes need to be made to the report to make this happen. I know I will have to change the query and add a join statement to get it from the customer record but am unable to figure out how to do it. Any ideas? Thanks, Nevermind, I figured it out myself. Thanks anyway. <nick@realkiteboarding.com> wrote in message news:BhKXd.15130$GO5.11570@fe07.lga... > Hey guys, > > My retail staff asked me if it is possible to a...

Unmatched Query Question
I have a database that I'm using to pull the names of students who have applied for graduation from an Excel spreadsheet, that is updated weekly, into an Access Table. Right now, I'm using an unmatched query to pull Student IDs who have not previously appeared on the Excel spreadsheet. At first, I was a little worried about this because if a student applies for graduation with a dual degree, their name shows up on the Excel Spreadsheet twice (see example below). 111000 Pig Porky BA.PSY 111000 Pig Porky BFA.FP Fortunately, as long as the student's ID number isn't on the Eval S...

Problem exporting/importing .psf
Hi guys, I searched the community before and couldn't find anything regarding this issue. I am trying to export an Outlook 2000 mailbox from one user account to another. However, when I try and import it, I get the message saying that "Personal Folders "Properties for this information service must be defined prior to use". I have looked up solutions for this and they say to make sure its not read only. I tried this and it doesnt work, its currently NOT read only. Please note: This is a computer where the Outlook folder source for the export is on a user account no...

Import Items from Quickbooks
Did anybody import items from QB? I would be specifically interested in how inventory is imported. Unfortunately there is no import template/mapping list available where I could choose which fields I want to import. I only would like to keep my descriptions, price, cost, vendor information, etc. in sync but NOT the inventory. After sales quantities cannot be sent back from RMS to QB I would worry that the inventory I have in QB (which is different to the inventory in RMS) will screw up the inventory in RMS during the load. Any comments will be greatly appreciated. Gerd ...

converting data
Have made an x-y scatter chart with temperature on the y axis and time on the x axis.The data was imported and the temperature is in centigrade.How can I convert the data into degrees fahrenheit? The temperature data is in column B and contained in over 400 cells The only way I know how to do this is manually converting each cell but this will take forever.Can anyone show me how to convert the entire column with a formula perhaps? -- sghioto ------------------------------------------------------------------------ sghioto's Profile: http://www.excelforum.com/member.php?action=getinfo&am...

Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...

Number Format Problem Prevents Proper Sorting
I have a long list of numbers that were exported from anothe application that I can't seem to format properly to sort in ascendin order. I have changed the format from General to Number and back wit no success. I'm sure that this is an easy fix, but I'm not seeing th answer. The third entry below (1103) should be toward the top of th list. Any ideas? 110272 110277 1103 110329 110338 11033 -- 65Stan ----------------------------------------------------------------------- 65Stang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1500 View this threa...

Import contacts from Excel into Outlook Contacts
I know this can be done from the import function in Outlook but how does one control where each field goes? There's a Map Fields step in the wizard. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Maurice" <anonymous@discussions.microsoft.com> wrote in message news:5e0a01c48154$f16ff9e0$a401280a@phx.gbl... > I know this can be done from the import function in > Outlook but how does one control where each field goes? ...

Convert Date Field to Text
I have created 2 tables, a Day table and Month table. I have two fields in both, Day table has Day, Text; Month table has Month, Text. I have a query combining the two so the query has Day, Days_Text, Month, Months_Text. What i wanted to do on one of my forms is have a date field using the short date so i can have the numeric value of course, is if the day of the month format is 'dd' or 'mm' it would look into either the table or query and bring back the text value, same for the month. Example: day '11', would read 'Eleventh', and month '09' would r...

Is it possible to schedule a publish to web in Publisher?
I want to insert a date field in my website. The date does not update unless I open the .pub files and publish to web everyday. Is is possible to schedule an automatic publish to web? Don, Do a google search for javascript and dates. However, almost every computer in the world (at least those running Windows) has the date on their computer. Why clutter up a web site with unneeded info? Mike Don wrote: > I want to insert a date field in my website. The date does not update unless > I open the .pub files and publish to web everyday. Is is possible to schedule > an automatic p...

Formatting Phone Numbers
Consider this example: A1 -> 3365551111 A2 -> 336-555-2222 A3 -> (336) 555-3333 I want to be able to scan through column A and change all numbers to be in the format of A1 which is just straight numbers...no punctuation. How would I do that? -- Tha BeatMaker ------------------------------------------------------------------------ Tha BeatMaker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23998 View this thread: http://www.excelforum.com/showthread.php?threadid=383980 Easiest would be to use a macro. Sub RemoveAlphas() ''...