2

New here. This is a commonly asked question but cannot seem to find the answer specific to my issue.

Just got a refurbished computer Windows 7 Professional 32bit. Installed Office 2003 and Firefox 41.0.2. One of my regular tasks is to copy rows of data from a web page and paste it into Excel. Some of the cells become merged but all appear as in the original web page EXCEPT for the last cell (also a merged cell). This cell contains a currency value. When pasted into Excel, it becomes text, and cannot be used for calculations. The cell in Excel is formatted as Currency, but after pasting, becomes Text.

Example : $4.90 pasted into Excel LOOKS like $4.90 but the value INCLUDES the "$" sign.

My other computers don't have this problem. I have tried almost all the other solutions found on the web eg. converting the cells to numbers, paste special with multiply by a cell containing the value 1 etc.

I've found that after pasting the data, if I just delete the "$" sign, it works fine.

  • In the Format Cell settings did you specify a symbol for the currency? – Leathe Nov 4 '15 at 7:23
  • yes. It's formatted as Currency (2 decimal places). Have tried changing to Number but also the same - Currency Data becomes text. – Andrew Nov 4 '15 at 7:45
  • So the problem then seems to be that Excel uses $ (as the first char) as absolute reference to a column. So when you copy-paste something with that character in that position, Excel doesn't really understand what it's meant to be and hence cannot convert it properly to a currency sign. The problem doesn't appear with e.g the euro sign (€), since it's at the end of the value. I'll take a look around to see if I can figure out something better than to remove the $ for every cell. – Leathe Nov 4 '15 at 8:05
  • Seems to work OK in LibreOffice Calc. And what are the "other computers"? Not Windows 7? – user3169 Nov 5 '15 at 6:58
  • One is a Windows XP machine (SP2). The other is a Windows 7 machine, but running Office 2010. The machine with the problem is a refurbished unit I just bought, running Windows 7 Professional and Office 2003. I've almost resigned myself to having to delete the "$" sign from this column for the rest of my life!!!! – Andrew Nov 5 '15 at 9:30
2

Solved the problem. Wasn't an Excel or Firefox issue.

Excel defaulted the currency symbol to my country's local currency (RM Malaysia) because this was the default setting when Windows 7 was installed. So the solution was to change the setting :

Control Panel > Region & Language > Formats Tab (Additional Settings) > Currency Tab > Changed (Local Currency RM) to $.

All is okay now.

I hope. ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.