tweak a formula from "contains" to "starts with"

ellison

Board Regular
Joined
Aug 1, 2012
Messages
184
Office Version
  1. 365
Platform
  1. Windows
Hi, a while ago I was given (on here!) an excellent formula for helping to look up if any strings of data in a Field that "contained" strings of data in another field.

I'll put an example below, assuming that Col A = "Old Info", Col C = "New Info", and in Col E = a formula which will check if any of the strings in Col C contain info that is in Col A:

The magic formula in Col E is =IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$4,C2))),"Y","N")

What I'd like to do now is tweak that formula so that the check is now for "begins with" as opposed to "contains"

Please find a table with some sample info showing the old formula and how it works:

[And what would be great is a formula which will do the same search BUT for ""begins with"]

i.e. The magic formula here correctly says that the New Info (in Col C) on Row 7 "contains" something in Col A [which would be ZC734]
But we would like a new formula for "begins with"...
So Row 7 would also be "N" because there is info in Col A which "starts" with BZ or BZV or BZV734 etc

Hope this helps!

Line IDCol A: Old InfoCol C: New InfoCol E: Status i.e. does the (this is the formula that is in E2)
1ZVN0106BZV374Y#=IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$5,C2))),"Y","N")
2ZV374ASSN01958141222Y
3MC04ZE047SMS996D1N
4SN0195814LT1004MH25883N
51111ZVN0106B16Y
654LS00BCAJCN
7BZV734Y
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,619
Office Version
  1. 365
Platform
  1. Windows
See if this does what you need,
Excel Formula:
=IF(ISNUMBER(LOOKUP(1,SEARCH(A$2:A$5,C2)/1)),"Y","N")
It looks ok with your example but I haven't tested beyond that.
 

ellison

Board Regular
Joined
Aug 1, 2012
Messages
184
Office Version
  1. 365
Platform
  1. Windows
See if this does what you need,
Excel Formula:
=IF(ISNUMBER(LOOKUP(1,SEARCH(A$2:A$5,C2)/1)),"Y","N")
It looks ok with your example but I haven't tested beyond that.
Oh my gosh that is BRILLIANT!!!!!

Huge thanks

Works a treat, lovely!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,987
Messages
5,575,386
Members
412,659
Latest member
oliverreyes
Top