How to check a list for single letters between text
 

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

How to check a list for single letters between text
 

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

How to check a list for single letters between text
 

Hi,

Put this in B1 and drag down

=LOOKUP(MID(A1,FIND("_",A1)+1,1),{"A","B","C"},{"A ttachment","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

How to check a list for single letters between text
 

=IF(NOT(ISERR(FIND("_A_",A1))),"Attachment",IF(NOT (ISERR(FIND("_C_",A1))),"Current","Batch"))


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159173

Microsoft Office Help

How to check a list for single letters between text
 

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" wrote in message
...
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

How to check a list for single letters between text
 

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" wrote in message
...
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