Find Specific Instance of Characters within a String and Return "This" Result

[censo]

Greetings,

I have a need to identify a specific set of characters "VQTY" within a string and return the result "89752".

Requirements:

- The returned value of "89752" will be static and always remain the same
- Regular instance of "QTY" (without the "V") should return nothing (cell remains blank)
- The number following "QTY" is irrelevant and should be ignored
- The instance of "VQTY" can appear anywhere within the string and the string varies in length

Below are some example of positive hits and the correct result when the criteria is not met (blank cells)

With the help of the community on a previous project I was working, the following function was created that may provide assistance on how to properly find the instance I'm looking to isolate (the calculation does not apply here, just the segment that helps identify the set of characters that will yield a match)

=LEFT(SUBSTITUTE(MID(A1&""-QTY1-"",FIND(""-QTY"",A1&""-QTY"")+4,9),""-"",REPT("" "",9)),9)*A2

Book1
	A	B
1	VALUE	RESULT
2	ABC-12345
3	ABC-12345A
4	ABC-12345-QTY2
5	ABC-12345-QTY2
6	ABC-12345-VQTY2	89752
7	ABC-12345-QTY8
8	ABC-12345C
9	ABC-12345-QTY9
10	ABC-12345-VQTY10	89752
11	ABC-12345-QTY11
12	ABC-12345-VQTY12	89752
Sheet1

 

[jtakw]

Hi,

Like this:

Book1
	A	B
1	VALUE	RESULT
2	ABC-12345
3	ABC-12345A
4	ABC-12345-QTY2
5	ABC-12345-QTY2
6	ABC-12345-VQTY2	89752
7	ABC-12345-QTY8
8	ABC-12345C
9	ABC-12345-QTY9
10	ABC-12345-VQTY10	89752
11	ABC-12345-QTY11
12	ABC-12345-VQTY12	89752
Sheet299

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(SEARCH("VQTY",A2)),89752,"")

 

[censo]

The solution works the exactly way I needed it to. Thanks so much!

 

[jtakw]

You're welcome, thanks for the feedback.