Excel Question - text not recognized in cells

[Nils]

I use the sumproduct formula and refer to cells containing text. In this
case, the cell can contain either 'Buy' or 'Sell'. Now sometimes Excel does
not recognize the text in the cell correctly. When I re-enter the text, it
does recognize it. How is this possible and how can I solve it in a
fundamental way?

 

[Pete_UK]

Perhaps you had leading or trailing spaces in those cells, or, if you
downloaded the data from a web site, maybe the non-breaking space
character (char 160). You can highlight the data, do CTRL-H (Edit/
Replace) and:

Find what: <space>
Replace With: leave blank
click Replace All

Then CTRL-H again, and:

Find what: Alt-0160
Replace With: leave blank
click Replace All

where Alt-0160 means hold the Alt key down and type 0160 on the
numeric keypad, then release Alt.

Hope this helps.

Pete

 

[Nils]

Pete, great, that's it!! For the next time: how can I get the Alt-0160?

Nils

 

[Ron Rosenfeld]

I use the sumproduct formula and refer to cells containing text. In this
case, the cell can contain either 'Buy' or 'Sell'. Now sometimes Excel does
not recognize the text in the cell correctly. When I re-enter the text, it
does recognize it. How is this possible and how can I solve it in a
fundamental way?

Another way of recognizing Buy or Sell, even if there is additional "stuff" in
the cell, is to use the SEARCH function (or FIND function if you need this to
be case sensitive).

e.g. =SUMPRODUCT(--ISNUMBER(SEARCH("Buy",A1:A10)),B1:B10)

--ron

 

[Peo Sjoblom]

You can't avoid them if you copy and paste from the web, if they are tables
you can use
data>import external data>new WebQuery


--


Regards,


Peo Sjoblom

 

[ShaneDevenshire]

Hi Nils,

I'm not sure what you mean by "how can I get the Alt-0160?" but you can
incorporate the TRIM and CLEAN functions into your SUMPRODUCT function. TRIM
removes trailing and leading spaces and all but one space between each word.
CLEAN removes some of the hidden characters. When both of these fail you can
employ SUBSTITUTE with CHAR(160).

If you supply a sample of your formula we can modify it.

TRIM(CLEAN(A1)) is a legal usage.

 

[Pete_UK]

Glad to hear that cleared it up.

Here is a formula solution to get rid of both types of space and
ensure that the result is a number:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ","")*1

Put this in a helper column and copy down. You will need to amend your
SP formulae to look at the column with this formula in, or you can fix
the values in the helper column and then paste them over the offending
cells (assumed in the formula to start with A1).

Hope this helps.

Pete