# Is the Last Name from Column D in Column C?

#### Nate Lawrence

##### New Member
In my job, we mail out forms to our customers.
One part of my job is to ensure that the name for the forms matches the shipping name.

If the two names don't match, I highlight them in our manifest so that our team stuffing the envelopes knows that a particular form and envelope are indeed a pair.

To avoid manually examining every single order in this regard, I did create a column after the last column in the worksheet to test whether the name on the form exactly matched the name on the address. (Our customers frequently include their middle initial or name to be printed on their form, but do not include it on their mailing address.)

This did reduce my workload, however even better would be if I could extract the last name from the Shipping Name (Column D) and test whether that extracted last name matches any part of the name for the form (Column C).

That way, the number of orders which I need to manually review decreases from 16 in 20 to perhaps 1 or 2 in 20, our turnaround time gets faster for our customers, my employers don't need to pay me for as much time, and I have more free time outside of work.

As an example, imagine that in C2 you have Nathanael D. Lawrence (for the form) and in D2 you have Nate Lawrence (for shipping).
Basically, I want to extract "Lawrence" from "Nate Lawrence" and test "Nathanael D. Lawrence" to see whether that value exists and return True or False in my formula column.

I did some searching this afternoon and did find a formula to extract the last name from Column D.
VBA Code:
``=TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))``

Where I'm failing is being able to take the result of that query (which seems to be working) and search for the identified name in Column C.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Marcelo Branco

##### MrExcel MVP
Try

=ISNUMBER(SEARCH(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100)),C2))

M.

#### Peter_SSs

##### MrExcel MVP, Moderator
We may possibly need more examples of what you have and what you expect as results. My reason being that with Marcelo's formula, which seems to be doing exactly what you asked, you could get quite misleading results - see rows 3 & 4 in column E below. Would you really want to show those as a match?

I Have suggested a slight modification in column F which eliminates those two TRUE values, but what about row 5 - what would you want to happen there? They are clearly different people but do have matching names.

Perhaps you could just compare the last name in each column - columnG?

21 09 21.xlsm
CDEFG
2Nathanael D. LawrenceNate LawrenceTRUETRUETRUE
3Jim JohnstoneKen JohnsTRUEFALSEFALSE
4Tim Johnson-KingKen JohnsTRUEFALSEFALSE
5Taylor SwiftRod TaylorTRUETRUEFALSE
Search Name
Cell Formulas
RangeFormula
E2:E5E2=ISNUMBER(SEARCH(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100)),C2))
F2:F5F2=ISNUMBER(SEARCH(" "&TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))&" "," "&C2&" "))
G2:G5G2=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),100))=TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))

Replies
2
Views
219
Replies
4
Views
442
Replies
0
Views
151
Replies
3
Views
342
Replies
1
Views
207

1,147,562
Messages
5,741,848
Members
423,691
Latest member

### 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