Forum Discussion
Text Filter
Warning: This is an experiment testing formulas using modern Dynamic Arrays.
n: = LEN(@testName); select a name from the column headers and determine its length
k: = SEQUENCE(1,n); define a sequence counter for characters
chr: = MID( @testName, k, 1 ); split the test name into separate characters
firstName: = LEFT( @testName, MIN( IF( chr=" ", k ) ) ); extract first name
lastName: = RIGHT( @testName, n - MAX(IF( chr=" ", k ) ) ); extract last name
Worksheet formula:
= ( LEFT(fullName, LEN(firstName)) = firstName) *
(RIGHT(fullName, LEN(lastName)) = lastName);
compares an array of full names against the test name.
| Tom B. Jones | James A Smith | |
| Tom H Jones | 1 | 0 |
| James B. Jones | 0 | 0 |
| Tom B Jones | 1 | 0 |
| Chris Williams | 0 | 0 |
| James Smith | 0 | 1 |
| James X. L. Smith | 0 | 1 |
Hi Peter,
Is there a way to extract the data without having to use test names? Each name in the two columns that I have are listed without any commas or other special characters as last name first name middle name/initial...
Hoping to find a way to rule out same names that have a middle name/initial listed...
For example, Hampton Sally E shows false to Hampton Sally Egg...
Any thoughts?
