UPC format-text with dashes and possible leading zero

[XL Rookie]

12 digits, may or may not have a leading zero. Needs dashes as follow: 0-12345-67890-1

client inputs the numbers. Leading number may or may not have a zero but if it does, it needs to be visible.

I was trying to do text but with dashes as a customer but couldn't quite get there.

thanks!
Kimberly

 

[Joe4]

Try custom format of:
0-00000-00000-0

 

[XL Rookie]

Try custom format of:
0-00000-00000-0

Ta-Da! I was completely overthinking that one!

Thanks!
Kimberly

 

[XL Rookie]

Try custom format of:
0-00000-00000-0

in testing, If a person miss-keyed and added too may or too few digits, what is the best way to accommodate that?

TIA,
Kimberly

 

[Joe4]

You are welcome.

It is important to know that Cell Formats only work on Numeric entries (don't work on Text entries).
That means that the entries really are numeric, meaning that the leading zero really isn't there (it is just formatted to look that way).
It is important to know that, because it could lead to confusion if you don't understand how it works.

For example, let's say that you have your 0-12345-67890-1 entry in cell A1.
Do you want to take a guess what this formula might return?
=LEFT(A1,3)
It will return "123" (not "0-1" or even "012").
That is because your entry is really 12345678901 with a Custom Format.

If you wanted to return the value with the leading zeroes and dashes to use for other purpose, you can use the TEXT function (which converts it to Text with that Custom format), i.e.
=TEXT(A1,"0-00000-00000-0")

 

[XL Rookie]

Thanks for the explanation. Again, I may be trying to overthink a problem that doesn't exist. Time will tell.

Kimberly

 

[Joe4]

The Custom Format will work fine.
I just wanted to make you aware of the other stuff in case you try doing anything with that value, and gets unexpected results.

 

[XL Rookie]

The Custom Format will work fine.
I just wanted to make you aware of the other stuff in case you try doing anything with that value, and gets unexpected results.

Thanks for being a great resource and a thorough teacher!