how replace comma with point in import data

I import some stocks prices from the web. They come in with a comma as 
decimal limitator and I need them to appear with a point instead. how can I 
convert it ? 
0
Utf
11/21/2009 7:06:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1669 Views

Similar Articles

[PageSpeed] 7

Set up your Windows Regional Options (in Control Panel) for the Format in 
which you want to read your input data.
Format Cells/ Number for the way that you want to display the output.
-- 
David Biddulph

"Gepetto" <Gepetto@discussions.microsoft.com> wrote in message 
news:2D214513-6C9B-4E14-8AAD-EF7012728197@microsoft.com...
>I import some stocks prices from the web. They come in with a comma as
> decimal limitator and I need them to appear with a point instead. how can 
> I
> convert it ? 


0
David
11/21/2009 7:21:19 AM
There are a couple of ways:
=SUBSTITUTE(A1,",",".")

Select the text you want to change, hit Ctrl+F, click the Replace tab, in 
the Find What box enter a , and in the Replace With box enter a . then click 
Replace All.

HTH,
Ryan--

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"David Biddulph" wrote:

> Set up your Windows Regional Options (in Control Panel) for the Format in 
> which you want to read your input data.
> Format Cells/ Number for the way that you want to display the output.
> -- 
> David Biddulph
> 
> "Gepetto" <Gepetto@discussions.microsoft.com> wrote in message 
> news:2D214513-6C9B-4E14-8AAD-EF7012728197@microsoft.com...
> >I import some stocks prices from the web. They come in with a comma as
> > decimal limitator and I need them to appear with a point instead. how can 
> > I
> > convert it ? 
> 
> 
> .
> 
0
Utf
11/21/2009 7:54:09 PM
Reply:

Similar Artilces:

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Power Point #2
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I just added new slides to my a PPpresentation, saved tham, and now I can't shut down. The three little ovals come up in a box with no words in them. The right hand one is blue. I click it and it just moves down to the center of the screen. Shoule I do a forced shut down or what? It would be better not to Force Quit unless you have to... Try pressing the ESC key to see if the dialog box goes away. If it does, then use File> Save to save you work, use File> Close to close the file, the either File> Quit o...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

exclamation point column
My register shows and envelope with a question mark in the column headed by the symbol ! In older versions I was able to get rid of this. I am having problems figuring out how to remove it in Money 2007. Any body have a solution? In microsoft.public.money, michaels wrote: >My register shows and envelope with a question mark in the column headed by >the symbol ! In older versions I was able to get rid of this. I am having >problems figuring out how to remove it in Money 2007. Any body have a >solution? No. You can keep it from looking for envelopes for the column ...

Excel QueryTables
I have code that queries a sql datasource, brings in my data to worksheet. Now, I am trying to use the formula sumproduct() t retrieve the data I need based on two criteria. My problem is that th data, the way that it is retrieved, is not readable by my formula o more so returns a value of 0 within the criteria of my formula. Now When i double click within the querytable (a specific cell) the data i converted to: formulaR1C1= "Text" and then becomes readable by th formula. Any suggestions would be helpful. Patric -- Message posted from http://www.ExcelForum.com ...

Importing Accounts and Contacts
Hi all... I have a HUGE list of accounts and contacts which need to be imported from Telemagic. I'm currently working on cleaning data, etc. My question is this. I plan on importing Contacts into CRM using the Import Tool. However, I see no way to import Accounts, only Contacts and Leads. I am trying to avoid using the CDF database since I am only importing Account and Contact information - no activities. Is there a way for me to import a list of Accounts first and then to do the import for the Contacts? Or will I have to use the CDF staging database? I saw another post that m...

Creating a Macro to Delete Commas
I have an excel file that the size will varry. I need a macro that wil check all the fields for a comma. If there is one I would like to ge rid of it. Does anyone have any idea how to do this? I have no idea an I have been assigned this task. Hel -- Message posted from http://www.ExcelForum.com Ron Why not use Edit>Replace. Just leave the "replace with" field empty Regards Mark Graesse mark_graesser@yahoo.co ----- rontap > wrote: ---- I have an excel file that the size will varry. I need a macro that wil check all the fields for a comma. If there ...

Point of Sale
Kind of an odd one here - customer installs Microsoft POS and can log in and operate the store on the day of install. They close down POS that night and the next morning they are unable to log back in. They get the following error message. "You could not be logged on. Your employee ID or password may not be correct, or the computer where your store database is located may be down or otherwise unavailable." The database is on the local machine - I have since had them do a complete uninstall of POS and MSDE and perform a re-install and then update POS - applying all hotfixes...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Source Data / Series
I have 8 charts in one of my workbooks in one of my files. Every month, I change the source data (series) to include the most recent month for all of the series within the chart. For example, I change: {from} =Sheet1!$V$18:$V$600 { to } =Sheet1!$V$18:$V$650 Here is the dilemma: WHEN I AM FINISHED CHANGING ALL THE SERIES IN THE CHART, I PRESS 'OKAY'. Instead of my cursor remaining where it was before I edited the chart, i.e. somewhere near the chart, THE CURSOR JUMPS TO THE BEGINNING OF THE WO...

retroactive deferral data to GL
Posting a Sales Retroactive Deferral does not populate the same columns in GL as if it were posted from Sales directly. This would be helpful for users and reports to have the data the same, regardless of it was retroactively entered or not. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

Word Mail Merge with Excel Data--Missing Data
I have created a mail merge document in Word2002, using Excel2002 for m data source. When I merge the document, some of my fields hav incomplete data. The only records affected are those where the dat from the Excel fields is fairly long. Only about 200 characters wil show up on the Word doc. I am not exceeding any limits in my Exce fields. I've tried everything I know to do, but can't get this one t budge. Any suggestions are welcome -- Message posted from http://www.ExcelForum.com The following posts by Word MVP, Cindy Meister, may help you: http://groups.google.com/grou...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Summing Data
I have an Excel Worksheet includes travel costs by employee by type of expense. These expense is allocate to cost centers based on the time each employee spends in the cost center. The worksheet lay out is: Col A Col B Col C Col E Col F Col G Col H Col I Employee Type of Exp Total exp 10-510 10-550 50-510 81-510 90-550 John Doe 5523 $500 $250 $50 $100 $0 $100 John Doe 5524 $250 $125 $25 $ 50 $0 $ 50 Mary Doe 5524 $...

No prior System Restore points
Since 2 days ago when I check System Restore, all it has is the most current restore point. In the past I would show about 2 months worth of prior restore points. A new one would be automatically created each day. Now only todays is there. I checked and the settings still look proper as to what they always were. System Restore is turned ON and it is set at %12 (max). What could have changed and how can fix it to not purge all prior points except the current one? On Apr 10, 3:21=A0am, mkenewbie <mkenew...@discussions.microsoft.com> wrote: > Since 2 days ago when I c...

Power Point slide show
How do I copy my pp slide show to a cd rom, I keep getting a this a read only file. thanks Roger ...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Data refresh
Hi, in Excel 2000, when you select to do a data refresh either by opening the doc or by selecting data refresh, is there a way you can protect the formattting style of the data I have set-up, eg the font size & style, cell size etc. I notice when I refresh it pastes it back unformatted. Thanks Hi Ludi I have found that the only way to overcome this problem is to record macro that refreshes the data and then formats as I want it to appear. I then link the macro to a button of the worksheet. - The 'bonus' i that you get to refressh the data in one less mouse click -- Messa...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Not recognizing a PST file import (2003)
Outlook keeps giving me an error message that PST files that I am trying to import into 2003 are not PST files. I did export them as PST files burned them to CD, moved them to new hard drive, removed the read-only designation. I also can't get my older version of Outlook 2000 to recognize them. Please help! Thanks m I had the same problem with Outlook 2003. Fortunately, I also copied the PST to a floppy disk (high density) and it worked fine from that copy. There appears to be a problem with Outlook 2003. And since it can potentially destroy years of contacts and e-mails, it's...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Unable to import into CRM using import wizard. Please help.
Hi , This is regarding the IMPORT WIZARD to import Contacts from an Excel sheet. I get the following error: "The ActiveX control did not download properly. The import is being stopped". Is is related to IE config? Any help will be greatly appreciated. Thanks in advance. Regards, -Sekhar You should allow to load ActiveX form your CRM-site in IE config. "Sekhar" <Sekhar@discussions.microsoft.com> wrote in message news:E3AF1792-50A6-49A5-A891-611A43B3A4DB@microsoft.com... > Hi , > > This is regarding the IMPORT WIZARD to import Contacts from an Excel she...

Importing bank downloads
I export transactions from my Virgin credit card accountand save to computer. When I go to import into money I told an error has occured and money will close down. After restart in accounts list under Virgin Credit Card it says 23 transactions to read . When I go into the account there is nothing there. I can download from other banks without any trouble. Hope someone can help ...