Formula Correction

[Pat]

Can you help me correct this formula.

=IF(New_Table!D2=TRIM(SUBSTITUTE(A1,MID(A1,MIN(FIND({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&"0123456789")),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.

 

[Rick Rothstein \(MVP - VB\)]

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&"0123456789"))-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]

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!

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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