Google Answers Logo
View Question
Q: Excel: Text compare and change ( Answered 5 out of 5 stars,   2 Comments )
Subject: Excel: Text compare and change
Category: Computers > Software
Asked by: vidgames-ga
List Price: $5.00
Posted: 07 Oct 2004 14:57 PDT
Expires: 06 Nov 2004 13:57 PST
Question ID: 411723
I'm currently working on a list of products with a variety of
categories for each, which I have in Excel (and will end up in Word).
I need to know if it's possible (and the Excel formula for making it
happen) to compare two fields in a row: If they're different, they
should stay as is, but if they're the same, I'd like them combined.

An example of this particular data in a row would be:

Author:<tab>author name<tab>Editor:<tab>editor name

If the author and editor are different names, it'll end up looking
like this in Word:

Author: John Doe
Editor: Joe Blow

But if they're the same, they'll be combined into:

Author/Editor: John Doe.

Can this be done using Excel formulas?

My preference would be to have the "Author: name" and "Editor: name"
end up as two fields, but I'm aware for the comparison, that process
will need four fields. So that I can keep the list in Excel with
everything lining up under the column heads, the end result of the
formula should either be:

Author: name<tab>Editor: name
Author/Editor: name<tab><blank field>

(I can take care of the process moving the data from Word to Excel
with the whole list, turn the two Author and Editor columns into the
four needed for comparison, then back to my preferred Word format
afterward; I just need the formula I can plug in to do the comparison
on each row.)

A nice tip will be provided for a speedy working solution. And, no
offense, but I don't need an explanation of how it works...just that
it works for my purposes, because I'm on a relatively short deadline.

Thanks in advance...

  A  E

Request for Question Clarification by tar_heel_v-ga on 07 Oct 2004 15:14 PDT
Can the names be in separate columns, ie:

Author Last   Author First  Editor First  Editor Last
Smith         James         Jones         Frank
Allen         Sam           Allen         Sam

Request for Question Clarification by tar_heel_v-ga on 07 Oct 2004 15:27 PDT
Let's give this a shot and let me know if this will meet your needs:

Row 1 Column A Author Name
Row 1 Column B Editor Name
Row 2 Column C =IF(A2=B2,"Editor/Author","Author")
Row 2 Column D =IF(C2="Editor/Author",B2,A2)
Row 2 Column E Empty
Row 2 Column F =IF(C2="author","Editor","")
Row 2 Column G =IF(C2="Editor/Author"," ",B2)

In row 2 and succeeding rows, column A will be the Author Name, Column
B will be the Editor Name

Copy the information in columns C-G all the way down the rows.

This will give you the following results:
If name is the same in A and B, the result will be "Editor/Author Name"
If name is different in A and B, the result will be "Author Name
<column> Editor Name"

If this will work for your format, let me know and I can "pretty it
up" so that the columns will be empty when there is no data, etc.


Clarification of Question by vidgames-ga on 07 Oct 2004 16:44 PDT
It didn't work perfectly, but I took what you offered and tailored it
(because it helped me understand what you were aiming for), and now it
works. I'm not actually using authors and editors, but rather company
names, so your first question of separating first and last name
wouldn't have worked.

Just for your purposes, this is what I have in my conversion sheet,
using the same author/editor example:

B1=<author name data>
D1=<editor name data>

Though I haven't tried it yet, my guess is I can make F1 just equal to
B1, because there's really not a comparison-and-change needed--it'll
always stay what it was. I also figure the G1 blank column can be
removed (it's just an extra tab that I'll have to find/replace out of
the final doc back in Word). And I took out the space in the quotes in
cell I1 because that just would have added an unnecessary space that
would have to be edited out of the final document.

Thanks much for 99.9% of the work. (hehe) I'm not sure what you have
to do next to get paid, but I'm happy with the results.
Subject: Re: Excel: Text compare and change
Answered By: tar_heel_v-ga on 07 Oct 2004 18:58 PDT
Rated:5 out of 5 stars

I am glad we were able to get what you were looking for.  Being that
we got you question taken care of in the above clarifications, I won't
repost.  If you have any additional help you need regarding this
project, let me know I would be more than happy to help.


vidgames-ga rated this answer:5 out of 5 stars and gave an additional tip of: $2.50
Thanks for the fast answer to help me out of the jam I was in. Google
Answers researchers rock...

Subject: Re: Excel: Text compare and change
From: tar_heel_v-ga on 08 Oct 2004 06:00 PDT

Thanks for the high rating and the generous tip.  Glad I could help!

Subject: Re: Excel: Text compare and change
From: aeh5a2-ga on 08 Oct 2004 22:23 PDT
You could have also used the Excel function of concatenate. 
Concatenate joins to referenced cells into one text.

You could use it in conjunction with an if statement.  i.e. 

If you try it you will see what I mean.  It might help you next time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy