Is the Last Name from Column D in Column C?

Nate Lawrence

New Member
Joined
Sep 24, 2019
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.

Thanks in advance for your help.
 

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
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try

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

M.
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,721
Office Version
  1. 365
Platform
  1. Windows
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))
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top