Custom number display format

[bEar]

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-

 

[David Biddulph]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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