How can I convert empty strings to empty cells?

[Shane]

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 before:

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]

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 before:
>
> 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

[Shane]

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 before:
> >
> > 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
>