UDF loop needed

[babsc01]

OK...my first post! I've developed a UDF that will extract a particular substring from a cell. I wanted to extract the 607xxxxx numbers from the cells below:

LOGFEE FOR 60735518LOGFEE FOR 60735548LOGFEE FOR 60735633

The 607xxxxx numbers could appear ANYWHERE in the string. So, I wrote the UDF to find the 607xxxxx numbers and pull them out:

Function GetString(FindWhat, InCell)
    If InStr(1, InCell, FindWhat) = 0 Then
    GetString = " "
    Else: GetString = Mid(InCell, InStr(1, InCell, FindWhat), 8)
End If
End Function

It seems to work fine. BUT...I've also got occurences where a 607xxxxx number appears more than once in the cell:
LOGFEE FOR 001969NW 60735884LOGFEE FOR 60735939 & 60735935LOGFEE FOR 60736093, 60736111 & 60736121

Can someone assist me with setting up the UDF to look for multiple occurences of a 607xxxxx number? I would be greatly in your debt.

Thanks!

[Richie(UK)]

Hi b,

I'm a bit pressed for time (and may amend the code later) but something like this:

Sub Test()
    Const strMain As String = "LOGFEE FOR 60735518LOGFEE FOR 60735548LOGFEE FOR 60735633"
    MsgBox GetString("607", strMain)
End Sub

Function GetString(FindWhat, InCell)
    Dim lCnt As Long, lCnt2 As Long
    If InStr(1, InCell, FindWhat) = 0 Then
    GetString = " "
    Else
    Cnt = 1
    Do Until InStr(lCnt, InCell, FindWhat) = 0
        lCnt2 = InStr(lCnt, InCell, FindWhat)
        GetString = GetString & Mid(InCell, lCnt2, 8) & " "
        lCnt = lCnt + lCnt2
    Loop
    End If
End Function

[babsc01]

Thanks, Richie. I'm not sure the original post presented my data in the way I wanted it to be seen. What shows as one line is actually several cells:

LOGFEE FOR 60735518
LOGFEE FOR 60735548
LOGFEE FOR 60735633

and

LOGFEE FOR 001969NW 60735884
LOGFEE FOR 60735939 & 60735935
LOGFEE FOR 60736093, 60736111 & 60736121
I will try the new code and let you know. Thanks again!

[Greg T]

This UDF is meant to be put in the cell immediately to right of the cell with the "607" number. Lock the column on the reference to rngCell [B1's formula is =GetString($A1,"607",8)] then you can drag to the right for as many columns as you think you need. The 1st 607# is in column B, the 2nd in C, the 3rd in D and so forth.

HTH

Function GetString(rngCell As Range, strFindWhat As String, intLen As Integer) As String
Dim rngCaller As Range, p As Integer
Set rngCaller = Application.Caller
p = 0
For i = 1 To rngCaller.Column - rngCell.Column
    p = InStr(p + 1, rngCell.Value, strFindWhat)
    If p = 0 Then Exit For
Next i
If p <> 0 Then
    GetString = Mid(rngCell.Value, p, intLen)
Else
    GetString = ""
End If
End Function

[babsc01]

Thanks, Greg! I will let you know the outcome.

[lucas]

Hey Greg,
Thats pretty slick. Works like a charm.

[babsc01]

EXCELLENT SOLUTION...exactly what I was looking for. Thanks for everyone for the help!