Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula correction in multiple workbooks | Excel Discussion (Misc queries) | |||
correction | Excel Discussion (Misc queries) | |||
correction of formula please............ | Excel Worksheet Functions | |||
Formula correction HELP!!! | Excel Worksheet Functions | |||
formula correction | Excel Worksheet Functions |