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

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