Formula Correction
 

Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code along
with a 4 digit number. The trim function works correctly to get rid of the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.

Formula Correction
 

Never a bad idea to show us samples of the data you have and what you are
looking to do with them.

Are all your Column A entries **always** letters followed by numbers? If so,
you can retrieve leading letter characters using this less complicated
function call inside your IF function calls...

LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789"))-1)

I am not sure you will need two IF function calls to do what I think you
might be trying to do, but it would be easier to tell if you showed us some
sample data and sample output you want from them (and any code translation
that are required).

Rick


"Pat" wrote in message
...
Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time
or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code along
with a 4 digit number. The trim function works correctly to get rid of
the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.

Formula Correction
 

Here is a sample of the data in Column 1 Rows 6 as per your request.

LT 3004
7769
9130
DLT 3026
7097
9284

I need a formula that strips the number and provides the following basic if
/ then results

If "DLT" then Part-Time Student
If "LT" then Part-Time Student
If "" then Full-Time Student

The results are printed in a separate column as I still need the student
number in the sreadsheet.

Thanks for your help!



"Rick Rothstein (MVP - VB)" wrote:

Never a bad idea to show us samples of the data you have and what you are
looking to do with them.

Are all your Column A entries **always** letters followed by numbers? If so,
you can retrieve leading letter characters using this less complicated
function call inside your IF function calls...

LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789"))-1)

I am not sure you will need two IF function calls to do what I think you
might be trying to do, but it would be easier to tell if you showed us some
sample data and sample output you want from them (and any code translation
that are required).

Rick


"Pat" wrote in message
...
Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time
or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code along
with a 4 digit number. The trim function works correctly to get rid of
the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.

Formula Correction
 

If "" then Full-Time Student

Not sure what you mean by that. A blank cell = Full-Time Student or a cell
with just a number = Full-Time Student.

Maybe this:

=IF(A1="","",IF(COUNTIF(A1,"*LT*"),"Part-Time Student","Full-Time Student"))


--
Biff
Microsoft Excel MVP


"Pat" wrote in message
...
Here is a sample of the data in Column 1 Rows 6 as per your request.

LT 3004
7769
9130
DLT 3026
7097
9284

I need a formula that strips the number and provides the following basic
if
/ then results

If "DLT" then Part-Time Student
If "LT" then Part-Time Student
If "" then Full-Time Student

The results are printed in a separate column as I still need the student
number in the sreadsheet.

Thanks for your help!



"Rick Rothstein (MVP - VB)" wrote:

Never a bad idea to show us samples of the data you have and what you are
looking to do with them.

Are all your Column A entries **always** letters followed by numbers? If
so,
you can retrieve leading letter characters using this less complicated
function call inside your IF function calls...

LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789"))-1)

I am not sure you will need two IF function calls to do what I think you
might be trying to do, but it would be easier to tell if you showed us
some
sample data and sample output you want from them (and any code
translation
that are required).

Rick


"Pat" wrote in message
...
Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time
or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code
along
with a 4 digit number. The trim function works correctly to get rid of
the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.

Formula Correction
 

Give this a try...

=IF(A1="","",IF(ISNUMBER(A1),"Full","Part")&"-Time Student")

Rick


"Pat" wrote in message
...
Here is a sample of the data in Column 1 Rows 6 as per your request.

LT 3004
7769
9130
DLT 3026
7097
9284

I need a formula that strips the number and provides the following basic
if
/ then results

If "DLT" then Part-Time Student
If "LT" then Part-Time Student
If "" then Full-Time Student

The results are printed in a separate column as I still need the student
number in the sreadsheet.

Thanks for your help!



"Rick Rothstein (MVP - VB)" wrote:

Never a bad idea to show us samples of the data you have and what you are
looking to do with them.

Are all your Column A entries **always** letters followed by numbers? If
so,
you can retrieve leading letter characters using this less complicated
function call inside your IF function calls...

LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789"))-1)

I am not sure you will need two IF function calls to do what I think you
might be trying to do, but it would be easier to tell if you showed us
some
sample data and sample output you want from them (and any code
translation
that are required).

Rick


"Pat" wrote in message
...
Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time
or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code
along
with a 4 digit number. The trim function works correctly to get rid of
the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.

Formula Correction
 

We can shorten that a little by using the SUM function instead of the
ISNUMBER function...

=IF(A1="","",IF(SUM(A1),"Full","Part")&"-Time Student")

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=IF(A1="","",IF(ISNUMBER(A1),"Full","Part")&"-Time Student")

Rick


"Pat" wrote in message
...
Here is a sample of the data in Column 1 Rows 6 as per your request.

LT 3004
7769
9130
DLT 3026
7097
9284

I need a formula that strips the number and provides the following basic
if
/ then results

If "DLT" then Part-Time Student
If "LT" then Part-Time Student
If "" then Full-Time Student

The results are printed in a separate column as I still need the student
number in the sreadsheet.

Thanks for your help!



"Rick Rothstein (MVP - VB)" wrote:

Never a bad idea to show us samples of the data you have and what you
are
looking to do with them.

Are all your Column A entries **always** letters followed by numbers? If
so,
you can retrieve leading letter characters using this less complicated
function call inside your IF function calls...

LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789"))-1)

I am not sure you will need two IF function calls to do what I think you
might be trying to do, but it would be easier to tell if you showed us
some
sample data and sample output you want from them (and any code
translation
that are required).

Rick


"Pat" wrote in message
...
Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIN D({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUM(LEN( A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") )"LT","Part
Time, or Temp
Student",IF(New_Table!D2="DLT"=TRIM(SUBSTITUTE(A1, MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),"") ),"Part-Time
or Temp Student","Regular Full Time Student"))

Assume the contents of Column A1 has a 2 - 3 character letter code
along
with a 4 digit number. The trim function works correctly to get rid
of
the
number and leaves only the letters. I then need to combine with an if
statement to get the results I need posted in the correct column.