Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
I have a report that I download into excel - it has a currency value and when
I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
Might be a better way but this is what I've down when faced with the old
number/text problem. Tools....options....error checking tab, make sure that 'number stored as text' is checked off. then 'tools'....'errorchecking' this should then show all of those numbers stored as text as errors. You should be able to hilight the whole column or row and then click on the error checking arrow and change them to numbers from text. "Erika" wrote: I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
Quite often downloaded data will have non-breaking spaces Character 160
attached to them that TRIM() does not remove. Try: =--SUBSTITUTE(A1,CHAR(160),) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
I tried this formula and the result I got was FALSE. I am not sure what that
means? "Sandy Mann" wrote: Quite often downloaded data will have non-breaking spaces Character 160 attached to them that TRIM() does not remove. Try: =--SUBSTITUTE(A1,CHAR(160),) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
I am not getting the green triangle that will let me convert the numbers to
text, that is what I was hoping to get but it is not showing up even with the feature on. "tim m" wrote: Might be a better way but this is what I've down when faced with the old number/text problem. Tools....options....error checking tab, make sure that 'number stored as text' is checked off. then 'tools'....'errorchecking' this should then show all of those numbers stored as text as errors. You should be able to hilight the whole column or row and then click on the error checking arrow and change them to numbers from text. "Erika" wrote: I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
Re reading my original message I spotted a part that was misleading on my
part instuctions wise. you need to make sure you have a check mark in the 'number as text' box. Is there a check in that box under tools, options, error checking? "Erika" wrote: I am not getting the green triangle that will let me convert the numbers to text, that is what I was hoping to get but it is not showing up even with the feature on. "tim m" wrote: Might be a better way but this is what I've down when faced with the old number/text problem. Tools....options....error checking tab, make sure that 'number stored as text' is checked off. then 'tools'....'errorchecking' this should then show all of those numbers stored as text as errors. You should be able to hilight the whole column or row and then click on the error checking arrow and change them to numbers from text. "Erika" wrote: I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
Worked perfect, thank you!
"Sandy Mann" wrote: I can onlt assume that it was because I did not include the third argument of the SUBSTITUTE() but it works fine for me in XL97. Try: =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I tried this formula and the result I got was FALSE. I am not sure what that means? "Sandy Mann" wrote: Quite often downloaded data will have non-breaking spaces Character 160 attached to them that TRIM() does not remove. Try: =--SUBSTITUTE(A1,CHAR(160),) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Downloaded Report
I'm glad that you got it to work. Thanks for the feedback telling us that
it worked. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... Worked perfect, thank you! "Sandy Mann" wrote: I can onlt assume that it was because I did not include the third argument of the SUBSTITUTE() but it works fine for me in XL97. Try: =--TRIM(SUBSTITUTE(A1,CHAR(160),"")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I tried this formula and the result I got was FALSE. I am not sure what that means? "Sandy Mann" wrote: Quite often downloaded data will have non-breaking spaces Character 160 attached to them that TRIM() does not remove. Try: =--SUBSTITUTE(A1,CHAR(160),) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Erika" wrote in message ... I have a report that I download into excel - it has a currency value and when I try to do a calculation on it - it will not work. I noticed that the values are not formatted as a number so I changed that. I also noticed there are some extra spaces in the field I tried the clean function, the trim function and convert text to columns to attempt to isolate and remove the extra spaces - none seemed to work. Any other suggestions to get these converted to text so calculations will work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i convert a downloaded template to UK | Excel Discussion (Misc queries) | |||
Updating of Downloaded Spreadsheet | Links and Linking in Excel | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
Header in Report Manager Report | Excel Discussion (Misc queries) | |||
downloaded template | Excel Discussion (Misc queries) |