Formatting question

[twallstr]

I am building a Excel form where my users will input a part number like 12345678. I want to format it so it looks like 1234-56-78. So far no problem (substitute function). The problem is that sometime the part number is 9 or more digits long like 1234567890. The output should then be 1234-56-7890.
How do I accomplish this?

Thanks in advance

 

[anglais428]

What is your current function?

 

[twallstr]

Hi anglais428!

This is the function I am using now.

=substitue(A1;"-";"")

 

[anglais428]

Assuming the number to be in cell A1 and you always want the first four numbers seperated, then the next two, then the remainder try

=LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,LEN(A1)-6)

 

[twallstr]

Excellent! Works perfectly!

Thanks for your help!