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]

Thanks, just did a quick test, couldn't get any output for the first formula.

That is because I misunderstood your question. I thought you were asking that for a given cell's text, whether another another piece of text existed within it as a stand-alone word. So if cell BC10 had "The baby was smothered in kisses" and BA10 had the word "other" in it, the formula would return FALSE because the word "other", while located within the text, it did not stand alone, rather, it was part of the word "smothered".

 

[Aladin Akyurek]

Have sorted column number:

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

Not sure, is this what you are looking for?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]B[/td][td]bud[/td][td]B[/td][td]yellowb[/td][td][/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

6​

[/td][td]B+[/td][td]xb[/td][td]x[/td][td]b+5[/td][td][/td][td][/td][td]

3​

[/td][/tr]
[tr][td]

7​

[/td][td]B) [/td][td]B x[/td][td]zB x[/td][td][/td][td][/td][td][/td][td]not available[/td][/tr]
[tr][td]

8​

[/td][td]B [/td][td]B x[/td][td]zB x[/td][td][/td][td][/td][td][/td][td]

1​

[/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H5 control+shift+enter, not just enter, and copy down:

=IFERROR(MATCH(B5,IF(LEN(B5)=1,C5:E5,IF(LEN(B5)=2,LEFT(C5:E5,2),"#")),0),"not available")