Adding a dash after the first five digits (for a ten digit number), and after the first five and ten digits (for a twelve digit number)

[Kibz Wahid]

[h=2]Hello all, I have a list containing alpha-numeric part numbers, some are 10 in number others 12.

My problem is how to insert a dash after the first five digits (where the part number has 10 digits) eg. 0446460340 to 04465-60340; and inserting a dash after the first five and ten digits (where the part number has 12 digits) eg. 99364-00850-8S

Thanks![/h]

<colgroup><col width="92"></colgroup><tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

Are you talking about a Custom Format, or you actually want the dashes physically inserted?
Do you want it to replace the current entry, or are you looking for a formula to put in another cell?

 

[Scott T]

Try. I am assuming that all Part numbers are ether 10 digits or12 digits. If any are a different length then this will not work.

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

 

[mikerickson]

If you want a formula
=TEXT(D1, "[<10000000000]00000-00000;00000-00000-00")

or you could use the format string in a custom number format.

 

[Kibz Wahid]

Welcome to the Board!

Are you talking about a Custom Format, or you actually want the dashes physically inserted?
Do you want it to replace the current entry, or are you looking for a formula to put in another cell?

Hello, yes - was thinking of a custom format. Have just seen Scott T's input; worked like a charm!

Thanks for the quick and friendly response!

 

[Rick Rothstein]

Hello, yes - was thinking of a custom format. Have just seen Scott T's input; worked like a charm!

If you were thinking of a custom format, then you should read Message #4 which tells you how to do it.

 

[Kibz Wahid]

If you want a formula
=TEXT(D1, "[<10000000000]00000-00000;00000-00000-00")

or you could use the format string in a custom number format.

I've tried the formula, works fine for a ten digit number; but cannot seem to get it to work on the 12 digits.

 

[mikerickson]

Try the custom number format
[<10000000000]00000-00000;00000-00000-00