Nasty Formula into user-defined Function..

[sutjh]

Hi, all,

I have a working formula which strips "." or "-" or "/" or returns the original text if none of these are found.

I need to turn this into a custom function, and can't figure out where I'm stuffing up...

The (really ugly, but working!) formula is:
=IF(ISERROR(RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))))),A1,LEFT(A1,IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))-1)&RIGHT(A1,LEN(A1)-IF(ISERROR(IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1))),FIND("-",A1),IF(ISERROR(FIND(".",A1)),FIND("/",A1),FIND(".",A1)))))

and my (best?)attempt at converting it is :

Public Function thStrip(InVal As String) As String
Dim strOutVal As String
Dim thFind As Excel.WorksheetFunction

Set thFind = Excel.Application.WorksheetFunction

With thFind
strOutVal = IIf(IsError(Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), _
.Find("-", InVal), IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))))), InVal, _
Left(InVal, IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), _
.Find(".", InVal))) - 1) & "" & Right(InVal, Len(InVal) - _
IIf(IsError(IIf(IsError(.Find(".", InVal)), _
.Find("/", InVal), .Find(".", InVal))), .Find("-", InVal), _
IIf(IsError(.Find(".", InVal)), .Find("/", InVal), .Find(".", InVal)))))
End With

thStrip = strOutVal

End Function

I suspect the problem may be in the concatenation, the use of IIf, IsError, or the way I have used Find...but I don't really
know !

I am trying to break it down and try and isolate the problem, but I haven't had any luck yet !

You may wonder why bother if the formula works, but I want to use the function in the design grid of a query, as in
thStrip(SuchandSuchField). I could bring back the data and then use the formula, but I need it for 24 columns with 1000+ rows, and this would be a more satisfying approach !

In any field there could be:

B9567.09 need:B956709
M20/4 need:M204
Z74-8 need:Z748
81.400 need:81400
I10 need:I10
gh/lo917 need:ghlo917
kl/7259 need:kl7259
100878-009 need:100878009

Any advice at all would be greatly appreciated !

t

[Jay Petrulis]

Hi,

No need for the UDF. If A1 is the test cell

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"-",""),"/","")

will do what you want.

Bye,
Jay

[Jay Petrulis]

If you really wanted the UDF

Function StripVal(MyString)
StripVal = WorksheetFunction.Substitute(WorksheetFunction.Substitute(WorksheetFunction.Substitute(MyString, ".", ""), "/", ""), "-", "")
End Function


Called as
=STRIPVAL(range) or
=STRIPVAL("string")
as in a regular function.

Bye,
Jay

[sutjh]

Thank You, Jay !!!!

That certainly cleared the decks !

I do still need to use a udf though as I want to use it in Access, which doesn't have Substitute (new one on me!)). The process should be considerable easier now, though ...

Thanks to you !!

t

[Yogi Anand]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"-",""),"/","")

I like that Jay ... BEAUTIFUL!

Yogi Anand