Looking at an IF function that will return the highlighted numbers

[itshere]

Best thought is an iF statement that if unit price is 1 it will be a 6 character/digit that starts with the AB followed by unit price. Thanks!

[JohnTopley]

Try

=MID(B2,FIND("AB",B2,1),6)

text in B2

[itshere]

is there an IF statement that can be derived from this?

[itshere]

issues with that because the second line already has AB12 so it uses that instead of the AB2000

[itshere]

Try

=MID(B2,FIND("AB",B2,1),6)

text in B2

issues with that because the second line already has AB12 so it uses that instead of the AB2000

[JeteMc]

Try the following:

=MID(SUBSTITUTE(B2,"AB","/",(LEN(B2)-LEN(SUBSTITUTE(B2,"AB","")))/2),SEARCH("/",SUBSTITUTE(B2,"AB","/",(LEN(B2)-LEN(SUBSTITUTE(B2,"AB","")))/2))+1,1)

Let us know if you have any questions.

[JohnTopley]

Function findAB(r As Range) As String
Dim t() As String
xstr = r
t = Split(xstr, " ")
For i = 0 To UBound(t)
    x = Trim(t(i))
    If Left(x, 2) = "AB" And Len(x) = 6 And IsNumeric(Mid(x, 3, 4)) Then
        findAB = x
        Exit Function
    End If
Next i
findAB = ""
End Function

=findAB(B2)

[Glenn Kennedy]

Next time... please read the yellow banner (top) and attach an Excel sheet to work with... not a picture of one.


=FILTERXML("<A><B>"&SUBSTITUTE(A2," ","</B><B>")&"</B></A>","//B[starts-with(., 'AB') and string-length()=6]")