I inserted numbers into a column and now want to format this column so that
it changes the numbers to appear as: 196-24-0-00-00-001-00-0-01 (ie, the -
appear in the same spot in each number)
I thought I could do it as a custom format but can't make it work. Thanks
for any help. Lisa

Hi Lisa

To keep the entry as number and to Custom format the cell as below then that
will display upto the 15th digit; and the rest will be zeros
000-00-0-00-00-000-00-0-0


Keep the entry as TEXT and then use a helper colum to display this as TEXT.
With your text in cell A1;try the below formula in B1
=TEXT(LEFT(A1,15),"000-00-0-00-00-000-00") & TEXT(RIGHT(A1,2),"-0-0")

If this post helps click Yes
---------------
Jacob Skaria


"Lisa W" wrote:

I inserted numbers into a column and now want to format this column so that
it changes the numbers to appear as: 196-24-0-00-00-001-00-0-01 (ie, the -
appear in the same spot in each number)
I thought I could do it as a custom format but can't make it work. Thanks
for any help. Lisa

A small correction.. (18 digits)

=TEXT(LEFT(A1,15),"000-00-0-00-00-000-00") & TEXT(RIGHT(A1,3),"-0-00")

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Lisa

To keep the entry as number and to Custom format the cell as below then that
will display upto the 15th digit; and the rest will be zeros
000-00-0-00-00-000-00-0-0


Keep the entry as TEXT and then use a helper colum to display this as TEXT.
With your text in cell A1;try the below formula in B1
=TEXT(LEFT(A1,15),"000-00-0-00-00-000-00") & TEXT(RIGHT(A1,2),"-0-0")

If this post helps click Yes
---------------
Jacob Skaria


"Lisa W" wrote:

I inserted numbers into a column and now want to format this column so that
it changes the numbers to appear as: 196-24-0-00-00-001-00-0-01 (ie, the -
appear in the same spot in each number)
I thought I could do it as a custom format but can't make it work. Thanks
for any help. Lisa

XL can only store 15 digits. As your number is 18 digits in length, no type
of number formatting will work. (note that you can't actually input
196240000000100001 into an XL sheet without the last 1 getting truncated)

You could use a custom format of:
000-00-0-00-00-000-00-0-00
or
###-##-#-##-##-###-##-#-##

but again, it will only work for the first 15 significant digits

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Lisa W" wrote:

I inserted numbers into a column and now want to format this column so that
it changes the numbers to appear as: 196-24-0-00-00-001-00-0-01 (ie, the -
appear in the same spot in each number)
I thought I could do it as a custom format but can't make it work. Thanks
for any help. Lisa

Obviously, your "number" is actually a text string of digits (if you entered
it as a number, Excel would have rounded it to 15 significant digits and put
zeroes in for the last 3 digits). You can't format a text entry the way you
want using Custom Formatting, but you can use VB code to achieve the same
effect. Right click the tab at the bottom of your worksheet, select View
Code from the menu that pops up and then copy/paste the following into the
code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Then Exit Sub
If Len(Target) = 18 And Not Target Like "*[!0-9]*" Then
Target = Format(Target.Text, "000-00-0-00-00-000-00-0-00")
End If
End Sub

Change the 3 in the first line of code to the column number that you have
these "numbers" in. Now go back to your worksheet and enter one of your
18-digit numbers into a cell in Column C (or whatever column letter
corresponds to the column you changed my example 3 value to)... it should
automatically format the way you want.

--
Rick (MVP - Excel)


"Lisa W" wrote in message
...
I inserted numbers into a column and now want to format this column so that
it changes the numbers to appear as: 196-24-0-00-00-001-00-0-01 (ie, the -
appear in the same spot in each number)
I thought I could do it as a custom format but can't make it work. Thanks
for any help. Lisa