Formatting a cell for Numerical Value

M

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi:

I am trying to customize some cells to display a number in a specific format. The format I want is as follows:

000-000000-0000-000000-000000-0000-0000

I tried to customize the the cell but end up with weird results. For example if a user types in 27123456 I want the formula o return:

027-123456-0000-000000-000000-0000-0000

The computer puts:

000-000000-0000-000000-000000-2712-3456


The number will ALWAYS start with 0 and end with 0000. Whatever number the user types in I want this info at the left hand side. ANy suggestions???

THANKS,
Mark
Sudbury , Ontario
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
I'm not sure you can do that with formatting alone

You could convert in another column using

=LEFT("0"&A1&REPT("0",39),39)

then format that as

000-000000-0000-000000-000000-0000-0000
 
Upvote 0
Let's say this for column A entries. Then format column A as Text, right click on the worksheet tab name, select View Code and paste this code in the resulting window:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myValue As String

    If Target.Column = 1 Then
        Application.EnableEvents = False
        myValue = Target & Left("000000000000000000000000000000000", 33 - Len(Target))
        Target = Format(myValue, "000-000000-0000-000000-000000-0000-0000")
        Application.EnableEvents = True
    End If
    
End Sub
This will change the entries automatically upon entering them.
 
Upvote 0
That seems to work. THANKS. Now one more thingcan that code be applied to a certain Range of cells or does it have to be applied to the whole sheet and column? My spreadsheet has a range named REV that has 10 lines it (at the moment). The amount of lines may change. Currently I need the code applied to D29:D39 The user may add more lines to the REV Range.

Take Care,
Mark
 
Upvote 0
Here is revised code where you can simply change the range reference so that it will be applied to whatever range you wish:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim myValue As String
    
'   Define range to apply
    Set myRange = Range("D29:D39")
    
    If Not (Intersect(Target, myRange) Is Nothing) Then
        Application.EnableEvents = False
        myValue = Target & Left("000000000000000000000000000000000", 33 - Len(Target))
        Target = Format(myValue, "000-000000-0000-000000-000000-0000-0000")
        Application.EnableEvents = True
    End If
    
End Sub
You want to set this range BEFORE entering the numbers/values.
 
Upvote 0
When you are in the Format Cells dialog box, select Custom and paste the following in the Type textbox:

"0"#-#####0"-0000-000000-000000-0000-0000"

When you save the workbook, this cell format type will also be saved.
 
Upvote 0
Hi:

I am having a little trouble getting this code to work in my 2 Ranges. Can this code be applied to 2 different Ranges? The 2 Ranges I have are:

MISC consists of $A$25:$G$35 the cells I want the formatting for would be E26 to E35 (this will change as more rows are added)

REV consists of $A$39:$E$49 the cells I want the formatting for would be E40 to E49 (this will change as more rows are added)

I played with the code a bit and could not get it to work. It was also popping up an error message regarding the first line of this code. I can't remember exactly what the error message was.

THANKS for your help,
Mark
 
Upvote 0
Hi JMiskey:

STRANGE I don't seem to be getting that error message anymore.?? It must of been something else that was causing the error message. However I still would appreciate direction on having your code appplied to the 2 different areas on my spreadsheet.

THANKS A Lot,
Mark :p
 
Upvote 0
There may be a more elegant way to handle non-continuous ranges, but this works:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim myRange2 As Range
    Dim myValue As String
    
'   Define ranges to apply
    Set myRange = Range("E26:E35")
    Set myRange2 = Range("E40:E49")
    
    If (Not (Intersect(Target, myRange) Is Nothing)) Or (Not (Intersect(Target, myRange2) Is Nothing)) Then
        Application.EnableEvents = False
        myValue = Target & Left("000000000000000000000000000000000", 33 - Len(Target))
        Target = Format(myValue, "000-000000-0000-000000-000000-0000-0000")
        Application.EnableEvents = True
    End If
    
End Sub
 
Upvote 0
Me AGAIN!!! I inserted the code and it seemed to be working and then I entered the following number into one of the cells your code applies to.

Entered:

123123456123412345612345612341234 which should reult in

123-123456-1234-123456-123456-1234-1234

Unfortunately the system is showing the following:

123-123456-1234-120000-000000-0000-0000

Any suggestions? Last but not least when I added more rows to the Range it the code didn't get applied to the new rows. Can the code be written to recognize that this formatting is to be applied to all rows (including added) in the Range.

THANKS for all your help,
Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

Gideon du Toit
  • Question
Impossible Text Format
Replies
5
Views
337
Joe4
Joe4
R
  • Solved
Exact Format Validation in either Data Validation or VBA
Replies
8
Views
680
Peter_SSs
Peter_SSs
M
  • Question
I need help with this code to modify the data into a table using Userform for VBA Excel (I want to do the range unlimited instead to do C7:C80000
Replies
5
Views
475
Dan_W
D
U
  • Solved
Using checkboxes to populate a value if they are checked
2
Replies
11
Views
911
kevin9999
K
G
  • Question
Find Values that Match Criteria from Different Sheet and Copy over to Another
Replies
0
Views
148
glaccounting
G

Forum statistics

Threads
1,215,492
Messages
6,125,115
Members
449,206
Latest member
burgsrus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top