How to check a list for single letters between text

[Hunt]

Hi everyone,

i need some help regarding a big list of entries and dont want to put the
data manually.

In my list i have some Filenames which contain different information, but
there are some significant letters which tells me, which file i have.

it's like: FirstNumber_A_Date.pdf
SecondNumber_B_Date.pdf
ThirdNumber_C_Date.pdf

There are only 5 different types of documents which are catagorized by those
letters, A, B and C

I want to create a =IF function which will write the name of Doc Type into a
seperate Column in the same sheet. Filenames are in A1 - A 400 and i want to
seperate the Type name (A = Attachment, B = Batch, C = Current) and fill the
full length of text into B1 - B400.

My problem is, i dont know how to check a single letter in this filenames, i
tried it with: =if (A1="*A*";"Attachment";"Batch") I just tried it that way
to check if this single letter thing works, but there is the next problem,
how do i should write this code, that he check if A and C aren't there, it
should give me the information for B. I need a general code therefore.

Hope it's easy to understand, hard to explain in a foreign language

thanks in advance and best regards, Hunt

 

[Gary''s Student]

Don't test for a single character, test for two !!

In VBA:

Sub marine()
Dim s As String
Dim findit As String
findit = "_A"
s = Range("A1").Value
If InStr(s, findit) > 0 Then
MsgBox "Its an attachment"
End If
End Sub


As a worksheet formula:

=IF(LEN(A1)<>LEN(SUBSTITUTE(A1,"_A","")),"its an attachment","")

 

[Mike H]

Hi,

Put this in B1 and drag down

=LOOKUP(MID(A1,FIND("_",A1)+1,1),{"A","B","C"},{"Attachment","Batch","Current"})

Mike

 

[Rick Rothstein]

And here is one more method for you to consider...

=CHOOSE(CODE(MID(A1,FIND("_",A1)+1,1))-64,"Attachment","Batch","Current")

 

[Rick Rothstein]

Here is a VBA two-liner that automatically selects the appropriate response
(without testing anything)...

Sub ABC()
On Error Resume Next
MsgBox Choose(Asc(Split(Range("A6").Value, "_")(1)) - 64, _
"Attachment", "Batch", "Current")
End Sub