Searching for partial matches or single characters

[aurelius89]

Can I MATCH a single character that may or may not be the only character in the cell?

For example,

=MATCH(BA10,BC10:BC12,0)

Cell BA10 = B

Cell BC10 = B
Cell BC11 = Bldkfhghj
Cell BC12 = FGB

Cells B10:B12 could be in any order and their content could be random as well.

In this case, this would return the value of 1, as expected. However, cell BC10 (Or any other cell in the range) could be B and a space, like this "B "

The formula now returns #NA as it doesn't find it.

=MATCH(BA10&"*",BC10:BC12,0)

Will not work as it will find "Bldkfhghj" as the first result if "Bldkfhghj" was this was before "B"

=MATCH("*"&BA10&"*",BC10:BC12,0)

Will not work as anything with B in it will be found

=MATCH("*"&BA10,BC10:BC12,0)

Will not work as it will find "FGB"

I am not sure how I can return the correct output here.
The "B" will either be on it's own or followed by a special character, never another letter.

This makes me think of something involving MID(cell ref,2,1)=CHAR(32) OR if MID(cell ref,2,1) is not between CHAR 65 and 90, or something to that effect might be involved.

Any ideas?

A different/better way of searching and returning a result is welcome, even if it's not a direct solution to this and it's something I can go on.

 

[Rick Rothstein]

Can I MATCH a single character that may or may not be the only character in the cell?

For example,

=MATCH(BA10,BC10:BC12,0)

Cell BA10 = B

Cell BC10 = B
Cell BC11 = Bldkfhghj
Cell BC12 = FGB

Cells B10:B12 could be in any order and their content could be random as well.

For the indicated cells...

=IF(ISNUMBER(SEARCH(BA10, BC10)), AND(NOT(ISNUMBER(FIND(UPPER(MID("="&BC10&"=", SEARCH(BA10, BC10), 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))), NOT(ISNUMBER(FIND(UPPER(MID("="&BC10&"=", SEARCH(BA10, BC10)+LEN(BA10)+1, 1)), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))

Note: This formula is case insensitive so B and b by itself is the same thing. Also, this formula will work for any text, not just single letters.

 

[Aladin Akyurek]

Care to concisely state what result you expect when

BA10 = B

for the target range below?

BC10 = B
BC11 = Bldkfhghj
BC12 = FGB
<strike></strike>
Perhaps a TRUE for each target cell?

 

[aurelius89]

It's simplified example really.
Ultimately that list is spread across separate columns and I want the output to be the column number of the result that is found.

Lets say:

Column A = Blue Man
Column B = Yellow
Column C = B (or "B " or "B+" or whatever)
Column D = Tube

The result should be 3

Ricks answer (Thank you) returns TRUE or FALSE and works correctly to identify the right match, just seeing if I can adapt that now.

 

[Aladin Akyurek]

So we are looking for B, right? If so, why C [3] but not either A [1] or D [4] as they also contain a B?

 

[aurelius89]

However, it doesn't work on a range, e.g BC10:BC12

Looking for B on its own, OR with any accompanying characters like space, or +, or /.
Accompanying characters will not be another letter (A-Z) though.

Should all be matches:
B+
B
B (With space)
B)
B$


Should not:
By
BB
Arb
ooobooo

 

[Rick Rothstein]

However, it doesn't work on a range, e.g BC10:BC12

If you are directing that comment to me, try making the cell with the text to find absolute. That means replace each instance of BA10 with $BA$10 (make sure you get them all) which I'll let you do because I am about to go to sleep.

 

[Aladin Akyurek]

However, it doesn't work on a range, e.g BC10:BC12

Looking for B on its own, OR with any accompanying characters like space, or +, or /.
Accompanying characters will not be another letter (A-Z) though.

Should all be matches:
B+
B
B (With space)
B)
B$


Should not:
By
BB
Arb
ooobooo

BC8 = B

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("|"&BC8&{")","+"," ","$"},"|"&$BC$10:$BC$12),$BC$10:$BC$12),"")

If you want B plus items like $, +, etc. anywhere in a target string, we have to remove the | sign:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(BC8&{")","+"," ","$"},$BC$10:$BC$12),$BC$10:$BC$12),"")

Note that this formula returns the entry it succeds to find, otherwise a blank.

 

[aurelius89]

Thanks, just did a quick test, couldn't get any output for the first formula.
The second worked, I just need to get a column number (4) out of this now.

 

[aurelius89]

Have sorted column number:

=MATCH(LOOKUP(9.99999999999999E+307,SEARCH(B5&{")","+"," ","$"},$C$5:$E$5),$C$5:$E$5),5:5)