IF, and LEFT, with wildcards...

[Tomkat53]

Greetings...
I have a formula that ALMOST works...
I need it to look at the LEFT 5 characters in Column C (label_description), and if they match the format "*** - *" then I need it to return just the first 3 characters.
If it's false, I need it to return "OB" & the RIGHT 3 characters from column A (label_code)
The problem is, I'm having difficulty getting the wildcards to work properly. Apparently "IF" cannot use wildcards, but COUNTIF can, so I have to combine them as follows...

As you can see the formula works until row 45... it WANT IT to return "OB506" but it returns "Car" because column C contains "*** - *" (where indicated by red rectangle.)
HOW do I get the formula to ONLY consider the LEFT 5 characters?
I have searched for a solution for quite awhile, but I seem to be running in circles.
I have hundreds of records in this table, and would really like to not have to "fix" each record manually.

 

[Fluff]

Try replace the *** with ??? that way it should look for 3 characters followed by a space & hyphen.

 

[Tomkat53]

Try replace the *** with ??? that way it should look for 3 characters followed by a space & hyphen.

Thanks for the quick reply, but that doesn't work ( I had already tried "?") as it just causes "FALSE" for every cell.

 

[Fluff]

Did you replace all the * with a ? or just the first three?

 

[Tomkat53]

All of them

 

[Fluff]

In that case just replace the first 3 & leave the rest of the formula as-is.

 

[Tomkat53]

OK, when I replace just the first 3 * with a ? - IT WORKS. BUT... WHY????

 

[Fluff]

An * represents any number of characters (including 0), whilst the ? represents one character only.

 

[Tomkat53]

OK, but why does it now work properly in row 45 in my example above?
Shouldn't "ngs - A" (??? - *) in the middle of the text string still trigger a match?

 

[Tomkat53]

After thinking about it.... I'm guessing the "COUNTIF" function BEGINS from left to right, by default?