PDA

View Full Version : Solved: UDF loop needed



babsc01
07-14-2004, 08:23 AM
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! :help

Richie(UK)
07-14-2004, 09:24 AM
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
lCnt = 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
07-14-2004, 10:25 AM
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! :hi:

Greg T
07-14-2004, 03:22 PM
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
07-14-2004, 05:23 PM
Thanks, Greg! I will let you know the outcome.

lucas
07-14-2004, 06:57 PM
Hey Greg,
Thats pretty slick. Works like a charm.

babsc01
07-15-2004, 05:59 AM
EXCELLENT SOLUTION...exactly what I was looking for. Thanks for everyone for the help!