# Compare two strings, and find difference?

#### EtherBoo

##### New Member
This may not be possible with excel, but this is what I'm trying to do

A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?

I need C1 to return:
how your

Is this possible?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### AlphaFrog

##### MrExcel MVP
Here's a custom function called WORDDIF that may do what you want.

To install the custom function...
• Alt+F11 to open the VBA Editor
• From the VBA menu, select Insert\ Module
• Paste the code below in the VBA Edit window

Back in Excel, put this formula in C1
=WORDDIF(A1,B1)
Code:
``````Function WORDDIF(rngA As Range, rngB As Range) As String

Dim WordsA As Variant, WordsB As Variant
Dim ndxA As Long, ndxB As Long, strTemp As String

WordsA = Split(rngA.Text, " ")
WordsB = Split(rngB.Text, " ")

For ndxB = LBound(WordsB) To UBound(WordsB)
For ndxA = LBound(WordsA) To UBound(WordsA)
If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
WordsA(ndxA) = vbNullString
Exit For
End If
Next ndxA
Next ndxB

For ndxA = LBound(WordsA) To UBound(WordsA)
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "
Next ndxA

WORDDIF = Trim(strTemp)

End Function``````

AlphaFrog,

Nice!

#### EtherBoo

##### New Member
WOW. Thank you 100 times over!

Now, I'm not sure if this is possible, but is it possible to do something where it will do something like this

A1: Jim-Steve-Paul-Phil
B1: Steve-Phil
C1: Jim--Phil

I'm going to make some edits to the original, concat function that made this, so I might not need the change, but it would still be a big help if possible!

Thank you so much again!

Cancel that request, I changed the concat function used to make these giant strings to have spaces, and now it's giving me exactly what I need.

Thank you SO much again!

Last edited:

#### Niv

##### New Member

Hello folks

I'm sorry for digging this old post out but, I need some help with that function in post 2. Original function works like a charm but can some helpful soul help me to modify it to do something like this

Cell 1: 1 2 3 4 5 6 7 8
Cell 2: 1 2 3 5 6 7 8
Outcome: - - - 4 - - - -

Replace same word with - and still show words that are different. All strings are 8 words long if it's relevant.

Niv

#### AlphaFrog

##### MrExcel MVP
Replace this one line...
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "

With this...
strTemp = strTemp & IIf(WordsA(ndxA) <> vbNullString, WordsA(ndxA), "-") & " "

#### Niv

##### New Member

Works perfect, you are an absolute star AlphaFrog. Thank You very much.

#### TwoCowz

##### New Member
AlphaFrog,

Sorry for resurrecting the old post!

First off, thank you for the amazing formula, its great to see coding in VBA at its best.

I have two strings which I compare nutritional values and they are all text lots of numbers and letters ,when I implemented your function, I receive a blank. What I am assuming is, somebody entered an extra space somewhere.

Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value, I think there's a function for it !

Cheers !

#### AlphaFrog

##### MrExcel MVP
Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value

I don't really follow what you're asking. Could you give some data examples the expected result like in the original question?

#### TwoCowz

##### New Member
When I have the two cells comparing

john jacob juinor
john jacob salt

I enter the A1 content as 1st string of words above, and the second one, the result is "junior" so it identified which value changed !

I want excel to return the character that is the letter "J" in the word junior. 11 to be exact purely for identification purposes as if I have a paragraph of ingredients I can spot where the recipe calls for sugar (packet) , sugar (cane).

Replies
5
Views
87
Replies
3
Views
107
Replies
6
Views
261
Replies
3
Views
84
Replies
0
Views
54

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,487
Messages
5,831,987
Members
430,100
Latest member
namhnz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back