Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
converting number to dollar
I know this should be easy but it isn't.
I copy html spreadsheets off a web link in an email that has columns of data. One of the fields has dollar amounts like 1,565.00 that are formatted as general. I need to convert this to a dollar amount like $1,565. When I try to format the field to currency, nothing happens. I tried copying and pasting values in all the different formats. I made sure the column isn't locked. I have 3,500 rows of different amounts that I don't want to manually change. Please tell me what I'm missing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting number to dollar
I know this should be easy but it isn't.
I copy html spreadsheets off a web link in an email that has columns of data. One of the fields has dollar amounts like 1,565.00 that are formatted as general. I need to convert this to a dollar amount like $1,565. When I try to format the field to currency, nothing happens. I tried copying and pasting values in all the different formats. I made sure the column isn't locked. I have 3,500 rows of different amounts that I don't want to manually change. Please tell me what I'm missing. Assuming you did Copy/Paste instead of Copy/Paste SpecialUnicode Text, the column with the dollar amounts was (by default) converted to text during Paste. Select a cell in the dollar amount col and look in the formula bar for a leading apostrophe... '1,565.00 ...whereby you need to use Copy/Paste SpecialUnicode to get Excel to use your format. I use this method when pulling data in from Word tables and it imports correctly. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
|
|||
|
|||
Quote:
The field looks like this "Total Bail Amount: 20,000.00". Yes these are arrest reports. I run a custom macro to format the 27 columns of information into something workable. In the macro I remove the alpha characters from this column. With or without the alpha characters, I don't see any leading apostrophe. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting number to dollar
Ah.., so the dollar amount are the result of parsing the original field
value. In this case Claus' suggestion is the way to go because it will work with resulting data from Copy/Paste as well. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
|
|||
|
|||
Very good suggestion but unfortunately it didn't work either.
Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting number to dollar
Hi,
Am Mon, 16 Sep 2013 20:12:13 +0100 schrieb mytyab: Very good suggestion but unfortunately it didn't work either. can you upload your file and post us the link? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting number to dollar
Claus' suggestion worked for me! I suspect you're not giving us
accurate details about the subject data!!! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
converting number to dollar
Hi,
Am Mon, 16 Sep 2013 06:30:00 +0100 schrieb mytyab: I copy html spreadsheets off a web link in an email that has columns of data. One of the fields has dollar amounts like 1,565.00 that are formatted as general. I need to convert this to a dollar amount like $1,565. When I try to format the field to currency, nothing happens. I tried copying and pasting values in all the different formats. I made sure the column isn't locked. I have 3,500 rows of different amounts that I don't want to manually change. click on the column header to select the column and format the column with your wished format and choose Data = TextToColumns = Fixed width = Finish Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting number to dollar | Setting up and Configuration of Excel | |||
convert number to words(like 1.00 = Dollar One Only) | Excel Worksheet Functions | |||
Question For Importing Data Into Excel/Converting General Number to Dollar Amount | Excel Discussion (Misc queries) | |||
Converting currency from US Dollar into Canadian dollars from column A to Column B | Excel Worksheet Functions | |||
Converting 100 cents to 1 dollar | Excel Worksheet Functions |