AnswerBun.com

How do I match a partial string in cell against a list Excel 2013?

I have the following information in a cell

| John Smith 34, Manager  | 
| Jane Doe 25, Assistant  |
| Lucio 32, Web Developer |

I also have another list with a list of names

| Lucio            |
| John Smith       |
| Jane Doe         |
| Samuel L Jackson |

[edited to clarify the question]

What I want to do is parse the data in the first set of cells, comparing the names with the information in the second set of cells and out out put a 1 if they match.

How would I do this in excel 2013?

EDIT: I think many of the answers have, due to my mistake, misunderstood that lists need to match cell by cell. I just need to make sure the names in the first list are correct and that they match at least one in the second list.

Super User Asked by Aasim Azam on November 27, 2021

4 Answers

4 Answers

Correct me if I am wrong, but the way I understand your question is that the subject text and search strings already exist in a particular order, and you would simply like to compare the two strings. i.e.

| John Smith 34, Manager  | John Smith |
| Jane Doe 25, Assistant  | Jane Doe   |
| Lucio 32, Web Developer | Lucio      |

I use the substitute() approach, to avoid overusing iferror(). You can comparing the length of the subject text ("haystack") vs the length of the haystack, substituting the search string ("needle").

= LEN(haystack) - LEN( SUBSTITUTE(haystack, needle, "") ) > 0

  // Analysis of a matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "John Smith", "") = " 34, Manager"
  - LEN(" 34, Manager") = 10
  - (22 - 10) > 0 = TRUE

  // Analysis of a non-matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "Lucio", "") = "John Smith 34, Manager"
  - LEN("John Smith 34, Manager") = 22
  - (22 - 22) > 0 = FALSE

In the example above, you would simply use =LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))>0. Also note that you can perform case insensitive comparisons via SUBSTITUTE(UPPER(haystack), UPPER(needle), "")

I prefer this approach over iferror(find(needle, haystack)>0,false), because the error handling is inherent -more of a philosophy that error handling should be done on the macro level rather than formulaic.

Additionally, if you prefer the binary output (0, 1) vs the Boolean output (TRUE, FALSE), you can wrap your function in INT() (i.e. INT(TRUE) = 1, INT(FALSE) = 0).

Answered by jtrumbull on November 27, 2021

If NameList refers to the range (e.g: $J$8:$J$10) that contains your list of names:

This formula must be array-entered:

=COUNT(FIND(NameList,A1))

To array-enter a formula, after entering the formula into the cell or formula bar, hold down while hitting . If you did this correctly, Excel will place braces {...} around the formula.

Answered by Ron Rosenfeld on November 27, 2021

From what you describe, the easiest way to do what you're asking for would be to write a VBA macro as the formula. But your question is too vague. For example you need to say if the first list contains names that are not in the second list. If the second list contains names not in the first list. Also in your example item 1 of first list matched item 1 of second list and so on for items 2 and 3. Are you just matching against the corresponding row in the second list or against the whole list? I could make assumptions but hopefully you get what I'm saying when I say that your question is too vague.

Answered by LorneCash on November 27, 2021

This might work for you.

If your first list is in A1:A3 and your second list is in D1:D3, then enter this in cell E1:

=IFERROR(IF(MATCH(D1,LEFT($A$1:$A$3,LEN(D1)),0)>0,1,0),0)

Use CTRL+Shift+Enter to make it an array formula. Then copy and paste down.

I checked it with a bad value in an extra cell D4=Donald and it will return 0 if there is no exact match.

EDIT:

I should probably explain that the way this formula works is that, it will take the original list A1:A3, and cut it down to the amount of characters that is in the cell we want to check it against D1.

The LEFT($A$1:$A$3,LEN(D1)) will come back with an array of 3 values:

{"John Smith";"Jane Doe 2";"Lucio 32, "}.

From this list, we are looking for John Smith and an exact match (case insensitive), and since there is only one, it returns 1.

In D2, for example, the resulting array of new values to check is:

{"John Smi";"Jane Doe";"Lucio 32"}

Since we're looking for Jane Doe, there is one match that exactly fits, and so 1 is returned.

Answered by Joseph on November 27, 2021

Add your own answers!

Related Questions

Multistep ssh – is it possible?

3  Asked on December 22, 2020 by vahagn-tumanyan

 

Microsoft Word: Table of Figures Ordering

2  Asked on December 21, 2020 by scoopsahaagendazs

 

not able to open my downloads through terminal in Mac OS

0  Asked on December 21, 2020 by daiwik-kashyap

   

Chrome high processor usage on Asus Tuf A15

0  Asked on December 20, 2020 by geeky-gurdeep

     

Lost files since renamed folder

1  Asked on December 20, 2020 by asklep

 

Renaming disks: from D: to F:

1  Asked on December 20, 2020 by user2925716

       

Windows 10 Taskbar Icons Blinking (Compressing)

1  Asked on December 19, 2020 by guy-thomas

     

Mysql Workbench import CSV 0 record imported

1  Asked on December 19, 2020 by zhenyu

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.