Custom cell format

[punchy]

I need to create a cell format that will automatically
translate this:

123123123412345612312123

INTO THIS:

123.123.1234.123456.123.12.123

BUT, the values in each segment will span the range
involved. For example, the first segment could be 123 or
it could be 999; and the same for all the other segments.

Ideally, the user should be able to enter the info with or
without the periods.

I can't figure it out. Can someone please help?

Thanks in advance for your assistance.

[jaf]

Hi,
Excel will only show 15 digits. Anything longer turns to zeros.
So your better off typing the dots. Excel will see that as text.
You can use "helper columns" to build a string and hide them if necessary.

123 123 1234 12345 123 12 123
=a1&"."&b1&"."& etc.

--

John

johnf202 at hotmail dot com

"punchy" <anon...@discussions.microsoft.com> wrote in message
news:032601c3bdd1$611d23a0$a401...@phx.gbl...

[Dave Peterson]

Or you could use a worksheet event and convert the text values to that format.

Format the range as text first or prefix each entry with an apostrophe ('1234).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String

On Error GoTo errHandler:

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
If Application.IsNumber(Target.Value) Then Exit Sub 'it has to be text!

myStr = Right(String(24, "0") _
& Application.Substitute(Target.Value, ".", ""), 24)

If IsNumeric(myStr) Then
Application.EnableEvents = False
Target.Value = Format(myStr, "000\.000\.0000\.000000\.000\.00\.000")
End If

errHandler:
Application.EnableEvents = True

End Sub

right click on the worksheet tab that should have this behavior. Select view
code. Paste this in.

It checks to see if the user only changed one cell, if the entry is text, and if
it's in column A. All those things must be true for it to continue.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm

--

Dave Peterson
ec3...@msn.com