CODEWITHSUNDEEP
excelexcel-formula
Justin Kelley
Justin Kelley

Reputation: 107

Formula to compare beginning of text in cells only

I have formulas in Excel looking for matches by using:

 =IF(A2=B2,"Yes","No)

I would like to count anything as a yes where the beginning of one matches the other (no matter how many characters). Some examples of what I would like to return "Yes":

  A2               B2
Justin Kelley     Justin K
Just              Justin
Justin K          Justin

Some examples of what should return a "No":

  A2               B2
Justin Kelley     Kelley Justin
Justin K          Justun K
Justin Kelley     Justin Ki

I would normally use the LEFT(1) function here, but it will not capture if there are any differences after the first letter.

Just a little background - there are system errors cutting-off some of my data. I only want to catch the human errors (the "No" returns).

Upvotes: 0

Views: 168

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Here you go:

=IF(IFERROR(LEFT(A2,LEN(B2))=B2,0)+IFERROR(LEFT(B2,LEN(A2))=A2,0),"Yes","No")

Upvotes: 3

Related Questions