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

Posted on 2012-08-22
Medium Priority
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:Alex Campbell
  • 5
  • 2
LVL 50

Expert Comment

by:Martin Liss
ID: 38322545
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

by:Alex Campbell
ID: 38322850
there is only one period as in the attachment names
LVL 50

Expert Comment

by:Martin Liss
ID: 38323164
What Im saying is that


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

This really has nothing before the period.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

by:Alex Campbell
ID: 38323215
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

by:Alex Campbell
ID: 38326291
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

Alex Campbell earned 0 total points
ID: 38330066
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

by:Alex Campbell
ID: 38412889
I did not receive any responses. I found the answer from another site.
Not asking for points.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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