exclusion formula?

  • Thread starter Thread starter Elwood Dowd
  • Start date Start date
E

Elwood Dowd

I'd like to know how to write a formula to pull out all the data from
a cell that's NOT in a corresponding cell. For example:

COL A COL B
Charles Dickens Tale of Two Cities Charles Dickens
Orson Scott Card Enders Game Orson Scott Card

Column A has name sof authors & books. Column B has the author's name
in it. Since Column A has no standardization of spacing, number of
breaks in a name, etc., I can't seem to get a regular LEFT, FIND, or
MID type function to work, but I'd like Column C to return all of the
data in Column A that's NOT in Column B (in this case, the book
titles).

How would I go about this?

thanks for any help,

Elwood
 
If the author's name always comes first use

=TRIM(MID(A1,LEN(B1)+1,32000))

where 32000 is just a big number to ensure that all the data is retained.
 
That works great on some of the entries, but what I get is like this:

FORMULA RESULT: Dickens Tale of Two Cities

Where it seems to have recoginized the first part of the name, but not
the second. I tried changing the +1 to other values, but can't find a
consistent fit. Column B has the name exactly as it appears in the
string in Column A (spaces included), but the formula seems to only
return the string after the first space, and not exclude the entire
bit.

Any ideas?

Thanks for your help so far!!

E
 
Did you really have "Charles Dickens" in B1?

I'm betting that you only had Charles (or maybe Dickens) in B1.

If you only have last names in column B, you could try this formula:

=TRIM(MID(A1,SEARCH(B1,A1)+LEN(B1),32000))

But if you have a mixture of names in column B, you'll have to have a different
solution.
 
What you say doesn't make sense.

If, as in your examples, B1 contains the full name of the author, as
does the entry in column A, then the formula can't produce what you say
it produces.

For the ones that don't work, what is the exact entry in column B and
the exact entry in column A?
 
OK, I think I see where the problem is, but I don't know a solution.
Here is exactly what I have:

CELL CONTENTS
-------- ----------------------------------------
A134 751-500 - Charles_dickens_tale_of_two_cities
B134 Charles_dickens

C143 formula > =TRIM(MID(A134,LEN(B134)+1,32000))

Result from formula: es_dickens_tale_of_two_cities


I'm assuming the problem has to do with the fact that there is a
number preceeding the author's name (in other words, that the name is
not the first thing in the cell), and that the LEN argument is just
counting the number of characters from the Left of the cell and
working from there? Since some cells do NOT have the preceeding
number, that adds to my problem, and that's why I hoped to be able to
pull out the info by its VALUE, and not its position.

Is that possible?

Either way, thanks for all of your help!

E
 
I think this works for me (see my reply below). THANKS!! That's what I
needed, and the addition of the SEARCH function seems to have done the
trick!

E
 
Back
Top