Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
Hey Guys and Gals,
I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
Try this:
=LEFT(A1,12-(MID(A1,12,1)="-")) OR =LEFT(A1,12-(RIGHT(A1,1)="-")) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... Hey Guys and Gals, I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
On Jun 7, 12:08 pm, "Ron Coderre"
wrote: Try this: =LEFT(A1,12-(MID(A1,12,1)="-")) OR =LEFT(A1,12-(RIGHT(A1,1)="-")) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... Hey Guys and Gals, I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help.- Hide quoted text - - Show quoted text - WOW thanks... I used the first one and it worked great. I still dont understand how it worked though. I'm getting stumped on the 12-mid() |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
I'm glad that helped.
Regarding: 12-(MID(A1,12,1)="-") This part returns the 12th character: MID(A1,12,1) This part test if the 12 character is a dash (-): (MID(A1,12,1)="-") The possible return values are TRUE and FALSE. In Excel, when an arithmetic operator is applied to a boolean value (TRUE/FALSE), TRUE becomes 1 and FALSE becomes zero. Consequently, if the 12th char is a dash....12-1...which equals 11 otherwise.....................12-0 I hope that helps. ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... On Jun 7, 12:08 pm, "Ron Coderre" wrote: Try this: =LEFT(A1,12-(MID(A1,12,1)="-")) OR =LEFT(A1,12-(RIGHT(A1,1)="-")) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... Hey Guys and Gals, I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help.- Hide quoted text - - Show quoted text - WOW thanks... I used the first one and it worked great. I still dont understand how it worked though. I'm getting stumped on the 12-mid() |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
The MID or RIGHT functions when compared with "-" will return TRUE if the
relevant character is a dash, or FALSE if it isn't. TRUE and FALSE are evaluated as 1 or 0 respectively, so the LEFT function takes 12 letters if it doesn't end with a dash, or 11 letters (=12-TRUE, =12-1) if the dash is to be removed. Easy when you know how. -- David Biddulph "Computer geek" wrote in message oups.com... WOW thanks... I used the first one and it worked great. I still dont understand how it worked though. I'm getting stumped on the 12-mid() On Jun 7, 12:08 pm, "Ron Coderre" wrote: Try this: =LEFT(A1,12-(MID(A1,12,1)="-")) OR =LEFT(A1,12-(RIGHT(A1,1)="-")) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... Hey Guys and Gals, I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function
On Jun 7, 12:55 pm, "Ron Coderre"
wrote: I'm glad that helped. Regarding: 12-(MID(A1,12,1)="-") This part returns the 12th character: MID(A1,12,1) This part test if the 12 character is a dash (-): (MID(A1,12,1)="-") The possible return values are TRUE and FALSE. In Excel, when an arithmetic operator is applied to a boolean value (TRUE/FALSE), TRUE becomes 1 and FALSE becomes zero. Consequently, if the 12th char is a dash....12-1...which equals 11 otherwise.....................12-0 I hope that helps. ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message oups.com... On Jun 7, 12:08 pm, "Ron Coderre" wrote: Try this: =LEFT(A1,12-(MID(A1,12,1)="-")) OR =LEFT(A1,12-(RIGHT(A1,1)="-")) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "Computer geek" wrote in message groups.com... Hey Guys and Gals, I need a little help with a function. I need to evaluate a cell and determine if the 12 character is a "-" (dash). And if it is a dash I need to strip it and if its not a dash I need to leave it alone. By the way the 12th character is the last character so its 12th from the left or 1st from the right. Seems to me I need to somehow nest a certain function inside an IF function. Please help.- Hide quoted text - - Show quoted text - WOW thanks... I used the first one and it worked great. I still dont understand how it worked though. I'm getting stumped on the 12-mid()- Hide quoted text - - Show quoted text - OK I get it now. I didn't know about the True becomes 1 and False becomes 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |