Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Nasty Formula into user-defined Function..

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

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

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

    will do what you want.

    Bye,
    Jay

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),"-",""),"/","")
    I like that Jay ... BEAUTIFUL!

    Yogi Anand

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •