Custom formatting 19-21 character long string with leading 0s

[aunakhtar]

Hi,

Our accounts numbers look like this: 0034-43070-3000-260-561-99

I am creating an excel file where the user, ideally, would want to type in the above account number without hyphens into a cell and then the formatting should automatically insert the hyphens.

Problem(s) I ran into are that if I convert the above string to number for custom format, Excel ignores anything beyond 15 digits and changes that to 0.
If I convert to text, then I cannot use custom format. I tried coming up with a formula but the leading 0s after each hyphen are making the formula very complicated (I am using the Left+Text functions).

Is there any simple way that the user either enters 3443070300026056199 or 003443070300026056199 and it converts to the required format?

Thanks in advance.

 

[eduzs]

Can be with vba macro?

 

[jtakw]

Hi,

A formula solution would be like this:

Book1
	A	B	C
1	003443070300026056199		0034-43070-3000-260-500-00
2	3443070300026056199		0034-43070-3000-260-500-00
Sheet30

Cell Formulas
Range	Formula
C1	=TEXT(A1,"0000-00000-0000-000-000-00")

 

[jtakw]

Also, alternatively, if you want to Format the cell so that it'll show as 0034-43070-3000-260-500-00 when either 003443070300026056199 or 3443070300026056199 is entered into the subject cell, use this as your Custom format for the Subject cells:

0000-00000-0000-000-000-00