Formula to compare 2 cells with different text

[shapenote]

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]

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]

Another way

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

 

[Peter_SSs]

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?