Formula to compare 2 cells with different text

shapenote

Board Regular
Joined
Jun 29, 2006
Messages
130
So I am trying to create a formula that compares to different cells to make sure the area assigned is correct. The problem is how the text is written ...

Cell B1 the text reads "Group 55 Region E Area 558"
in Cell B2 the text reads "55E8"

I need a formula that compares B1 to B2 that shows 55E8 is the a match of the Region and Area.

As you can see B1 basically is saying it is Group 55, Region E, Area 8.

Thoughts?
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
607
Hi Shape,

As long as the format of B1 doesn't change this is working for me;

=IF(B2=MID(B1,7,2)&MID(B1,SEARCH("REGION ",B1)+7,1)&RIGHT(B1,1),"Correct","Incorrect")
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,679
Office Version
2007
Platform
Windows
Another way

=IFERROR(IF(SEARCH(LEFT(B2,2)&"*"&MID(B2,3,1)&"*"&RIGHT(B2,1),B1),"Yes"),"No")

 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,683
Office Version
365
Platform
Windows
Thoughts?
Could we have a few more examples & results, including some that are incorrect so we can see in what ways they might be incorrect?

I'm asking since, for example, if it was possible to have this ..

B1: Group 55 Region E Area 588
B2: 55E8

.. then both the suggested formulas say this is right but it doesn't look right to me. But perhaps such data is not possible or if it is maybe it is correct anyway? More examples and explanation might help clarify.


Also, are group, region and area always 2, 1 and 1 characters long respectively?

Do the words "Group", "Region" & Area" always appear in B1, and in that order and with no other text apart from the 3 bits of data we are trying to check?
 

Forum statistics

Threads
1,077,662
Messages
5,335,564
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top