Custom number display format
 

I need 19 digits to read in a specific hyphenated format (no formula):
1234-5678-9012-3456789
but after using the following two custom formats:
0000-0000-0000-0000000 and ####-####-####-#######

it kept giving me this:
1234-5678-9012-3450000

How can I get it to put hyphens where I need them without clearing my last
for digits?

Thanks-

Custom number display format
 

Either format the cell as text or precede the data with an apostrophe, then
in either case type in the text string (including the hyphens). You need a
text string, as Excel numbers are limited to a precision of 15 significant
figures.
--
David Biddulph

"bEar" wrote in message
...
I need 19 digits to read in a specific hyphenated format (no formula):
1234-5678-9012-3456789
but after using the following two custom formats:
0000-0000-0000-0000000 and ####-####-####-#######

it kept giving me this:
1234-5678-9012-3450000

How can I get it to put hyphens where I need them without clearing my last
for digits?

Thanks-

Custom number display format
 

On Thu, 15 May 2008 07:53:00 -0700, bEar
wrote:

I need 19 digits to read in a specific hyphenated format (no formula):
1234-5678-9012-3456789
but after using the following two custom formats:
0000-0000-0000-0000000 and ####-####-####-#######

it kept giving me this:
1234-5678-9012-3450000

How can I get it to put hyphens where I need them without clearing my last
for digits?


You must enter your data as text.

Then you can format it with a VBA macro.

Here's an example of one that puts the reformatted string in the adjacent
column, but you could also use it as an event-triggered macro, or use it in
other ways:

================================
Option Explicit
Sub SpecFormat()
Dim c As Range
For Each c In Selection
With c.Offset(0, 1)
.NumberFormat = "@"
.Value = Replace(c.Value, "-", "")
.Value = Left(.Value, 4) & "-" & _
Mid(.Value, 5, 4) & "-" & _
Mid(.Value, 9, 4) & "-" & _
Mid(.Value, 13)
End With
Next c
End Sub
==================

--ron

Custom number display format
 

Here's an example of one that puts the reformatted string in the adjacent
column, but you could also use it as an event-triggered macro, or use it
in
other ways:

================================
Option Explicit
Sub SpecFormat()
Dim c As Range
For Each c In Selection
With c.Offset(0, 1)
.NumberFormat = "@"
.Value = Replace(c.Value, "-", "")
.Value = Left(.Value, 4) & "-" & _
Mid(.Value, 5, 4) & "-" & _
Mid(.Value, 9, 4) & "-" & _
Mid(.Value, 13)
End With
Next c
End Sub
==================

A minor simplification...

Sub SpecFormat()
Dim c As Range
For Each c In Selection
c.Offset(0, 1).Value = Format(Replace(c.Value, "-", ""), _
"0000-0000-0000-0000000")
Next
End Sub

Rick

Custom number display format
 

On Thu, 15 May 2008 17:59:30 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

A minor simplification...

Sub SpecFormat()
Dim c As Range
For Each c In Selection
c.Offset(0, 1).Value = Format(Replace(c.Value, "-", ""), _
"0000-0000-0000-0000000")
Next
End Sub

Rick

Nice. I didn't think to use Format because I was thinking in Excel, where
formats (and the Text worksheet function) applies to numbers, and not strings.
--ron