Need Excel UDF to give different values based on text in file name

Posted on 2012-08-22
Last Modified: 2012-09-19
I need a function that will look at file names and based on values in the file name, return different named values such as Comment Form, Completed Comment Form, Combined Comment Form and Document.

I have looked at CASE, but I'm not quite sure what to do.

If a file name has CST and "-Combined." (example: AAA-ABC-XY-CST-001-01-Combined.doc) then it is a Combined Comments file.

If a file name has CST and there is nothing before the period in the file name (ABC-XY-CST-001-01.doc) then it is a blank Comment Form.

If a file name has CST and there IS something other than 01, 02, etc. before the period in the file name (ABC-XY-CST-001-01-John.doc, ABC-XY-CST-001-Martha.doc) then it is Comment Completed Form.

The file names are:
Major Project Acronym 3 letters (AAA)
a dash
project acronyms of varying length(ABC, CDEF, LMNOP)
a dash
document type of varying length (XY, WXYZ, VONOM)
a dash
some of the files have CST and but will also get various additions to the name (see below) but some don't have CST
a dash
three numbers 001, 002, 003, etc.
a dash
two numbers 01, 02, 02
some of the CST files have something before the period
a period
three to four letter document extension (.doc, .xls, .xlsm)
Question by:Alex972
    LVL 44

    Expert Comment

    by:Martin Liss
    You give as an example the following"

    If a file name has CST and there is nothing before the period in the file name (ABC-XY-CST-001-01.doc) then it is a blank Comment Form.

    But there is something before the period and that's '001-01'. Is your example correct?

    Author Comment

    there is only one period as in the attachment names
    LVL 44

    Expert Comment

    by:Martin Liss
    What Im saying is that


    has something before the period and that's -001-01.

    This really has nothing before the period.


    Author Comment

    sorry i see what you mean
    if it has cst and there nothing *between* 001-01 and .doc then it is a blank comment form
    if a cst has ...001-01-alex.doc then it is a completed comment form

    Author Comment

    I worked out the assignment of values in a formula, but I would like to see how it would be done as IF statements or as a CASE statement

    =IF(ISERROR(FIND("CST",A2)),"Not CST",IF(ISERROR(FIND("Combined",A2)),IF(MID(A2,FIND("CST",A2)+10,1)<>".","Comment Entry","Blank Comment"),"Combined CST"))

    The formula applies to the attached spreadsheet.

    Accepted Solution

    Got answer much faster from another source.
    I don't deserve any points, but here is the solution:
    Function CSTType(rng As Range) As String
        If rng.Value Like "*CST*Combined*" Then
            CSTType = "Combined CST"
        ElseIf rng.Value Like "*CST???????.*" Then
            CSTType = "Blank Comment"
        ElseIf rng.Value Like "*CST*" Then
            CSTType = "Comment Entry"
            CSTType = "Not CST"
        End If
    End Function

    Author Closing Comment

    I did not receive any responses. I found the answer from another site.
    Not asking for points.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Excel 2016 - What is this arrow pointing to a cell? 9 45
    Posting V12 2 16
    Sum Per Month 7 23
    rank minimum order 9 18
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now