• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a formula

Davealot

Davealot

Member
Needing a moment of help if possible. I have one customers with varying part numbers, some have 10 digits, some have 12.

For example:
8791004190
8791004190a0

I've wrote a formula to attempt to place a dash every 5 digits for example:
87910-04190
87910-04190-a0

This is what I've wrote, with the intentions that the LEN formula would see if the cell was greater than 10 digits and if so return the final two digits and if not just return the ten digit modified part number:

=LEFT(A4,5)&"-"&MID(A4,6,5)&(IF(LEN(A4=12),RIGHT(A4,2),""))

However what is being returned on the ten digit numbers is
87910-0419090

Not sure what I'm doing wrong, specifically I know that I need to figure out how to correctly get it to tell the difference between a ten digit and twelve digit part number, and not sure how to correctly designate the second "-" in a twelve digit part number. Thanks for your help
 
That does add the dash in, I was putting it in the wrong place and finally got mad and just dropped it from the formula, the problem is that on ten digit part numbers it still pushes back as: 87910-04190-90, It's still taking the last two digits and placing them again for 10 digits, and I'm trying to work the IF&LEN formulas to only do that for 12 digit part numbers, Thanks for the help though my friend it is greatly appreciated!
 
once check it.it's working exact what u desire
=LEFT(C15,5)&"-"&MID(C15,6,5)&(IF(LEN(C15=12),"-"&MID(C15,11,5)))
 
You must log in or register to reply here.
Back
Top