Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom cell format
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom cell format
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom cell format
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 punchy wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cell custom Number format | Excel Worksheet Functions | |||
Format Cell as custom type but data doesn't display like I custom. | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
Custom format that shows blank cell if another cell is empty | Excel Discussion (Misc queries) |