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

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","")

--
Gary''s Student - gsnu200909


"Hunt" wrote:

> 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

Hi,

Put this in B1 and drag down

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

Mike

"Hunt" wrote:

> 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

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

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

--
Rick (MVP - Excel)


"Hunt" <(E-Mail Removed)> wrote in message
news:62F7DD8A-8B37-4908-B46A-(E-Mail Removed)...
> 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

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

--
Rick (MVP - Excel)


"Gary''s Student" <(E-Mail Removed)> wrote in message
news:28286BEE-FDCE-4DE1-9C20-(E-Mail Removed)...
> 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","")
>
> --
> Gary''s Student - gsnu200909
>
>
> "Hunt" wrote:
>
>> 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