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.

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.

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

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

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 -

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