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-

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-

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

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

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