How can I convert empty strings to empty cells?
Hi there.
Hopefully someone can help with something that seems pretty strange. I'm exporting data from access to excel and doing some stuff to the data with pivot tables. In the pivot tables however, blank cells don't register as 'blank' unless I manually go into each cell and hit delete (even though there seems to be nothing there to delete). Here's an example of befo Count of QualityChecked QualityChecked Total (blank) 4 Yes 6 Grand Total 10 and after I delete the data in the seemingly blank cells: Count of QualityChecked QualityChecked Total (blank) Yes 6 Grand Total 6 Someone in the access group mentioned that the empty fields from access are appearing in excel as empty strings (as opposed to empty cells) and that you guys might be able to help me with resolving that. It's one of those strange things that's driving my insanity to the edge of oblivion. Thanks in advance. Shane |
First, I don't use Access and if the data is coming from Access, this may not
work. But if I have a formula that evaluates to "", then convert to values. That cell isn't seen as empty by excel. I like to select the range (whole column or whole worsheet???) and do: Edit|Replace what: (leave blank) with: $$$$$ (some unique string) replace all followed by: Edit|Replace what: $$$$$ (that same unique string) with: (leave blank) replace all ======= If you put =len(a1) (Point at an offending cell), do you get 0? If no, you may want to find out the character that's in that cell. You can use Chip Pearson's Cell View addin to find out the character it is: http://www.cpearson.com/excel/CellView.htm Shane wrote: Hi there. Hopefully someone can help with something that seems pretty strange. I'm exporting data from access to excel and doing some stuff to the data with pivot tables. In the pivot tables however, blank cells don't register as 'blank' unless I manually go into each cell and hit delete (even though there seems to be nothing there to delete). Here's an example of befo Count of QualityChecked QualityChecked Total (blank) 4 Yes 6 Grand Total 10 and after I delete the data in the seemingly blank cells: Count of QualityChecked QualityChecked Total (blank) Yes 6 Grand Total 6 Someone in the access group mentioned that the empty fields from access are appearing in excel as empty strings (as opposed to empty cells) and that you guys might be able to help me with resolving that. It's one of those strange things that's driving my insanity to the edge of oblivion. Thanks in advance. Shane -- Dave Peterson |
Dave, you're a legend. it's working like a dream.
Thanks mate "Dave Peterson" wrote: First, I don't use Access and if the data is coming from Access, this may not work. But if I have a formula that evaluates to "", then convert to values. That cell isn't seen as empty by excel. I like to select the range (whole column or whole worsheet???) and do: Edit|Replace what: (leave blank) with: $$$$$ (some unique string) replace all followed by: Edit|Replace what: $$$$$ (that same unique string) with: (leave blank) replace all ======= If you put =len(a1) (Point at an offending cell), do you get 0? If no, you may want to find out the character that's in that cell. You can use Chip Pearson's Cell View addin to find out the character it is: http://www.cpearson.com/excel/CellView.htm Shane wrote: Hi there. Hopefully someone can help with something that seems pretty strange. I'm exporting data from access to excel and doing some stuff to the data with pivot tables. In the pivot tables however, blank cells don't register as 'blank' unless I manually go into each cell and hit delete (even though there seems to be nothing there to delete). Here's an example of befo Count of QualityChecked QualityChecked Total (blank) 4 Yes 6 Grand Total 10 and after I delete the data in the seemingly blank cells: Count of QualityChecked QualityChecked Total (blank) Yes 6 Grand Total 6 Someone in the access group mentioned that the empty fields from access are appearing in excel as empty strings (as opposed to empty cells) and that you guys might be able to help me with resolving that. It's one of those strange things that's driving my insanity to the edge of oblivion. Thanks in advance. Shane -- Dave Peterson |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com