Find and Sort Data

sandramac
sandramac used Ask the Experts™
on
Hello, trying to build a formula or macro to perform the following:
In Column A, find KQSA
Then in that column, find the letters "TK", then extrapolate the two digit number before it.  IF there is no TK, then 0.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I assume that you are looking for a cell in column A that contains only KQSA. And on that same row, there is a cell containing TK with two digits before it. You want those two digits.

While it can be done with a formula, it would be substantially easier with a user-defined function. You can then use a formula like:
=FindTK(A:Z,"KQSA","TK",2)             search columns A:Z, look for row with KQSA, then get 2 digits before letters TK

Paste the code in a regular module sheet. Use the Fx icon at left of formula bar to display the function wizard. Choose "User-defined" as the function type, then enter your data. Using the function wizard isn't required, but doing so (first time only) preserves the capitalization of the function name.

Function FindTK(rg As Range, rowFind As Variant, colFind As Variant, nDigits As Integer)
Dim i As Variant, j As Variant
Dim k As Long
FindTK = 0
On Error Resume Next
i = Application.Match(rowFind, rg.Columns(1), 0)
If IsError(i) Then Exit Function
j = Application.Match("*" & colFind & "*", rg.Rows(i), 0)
If IsError(j) Then Exit Function

k = InStr(1, rg.Cells(i, j).Value, colFind)
If k > nDigits Then FindTK = Mid(rg.Cells(i, j).Value, k - nDigits, nDigits)

End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial