Bank Account Format - Data Validation

[tlc53]

Hi there,

I'm trying to set the format of the cell to format numbers into the following;

00-0000-0000000-000

So when 16 numbers are entered (no hyphens), it automatically comes out looking like this, for eg.

12-1234-1234567-123

But if only 15 numbers are entered, the third to last number becomes a zero. So for eg.

12-1234-1234567-012

As I would like to restrict the data entry length to only 15 or 16 numbers, I guess I would need to do this in data validation. Has anyone done something like this before? Not sure where to begin..

Thank you!

 

[Twollaston]

Use Kweavers formula

To restrict the value, you can use data validation -> Text Length -> Between 15 and 16

 

[kweaver]

If you put in your 15 or 16 digit numbers as TEXT, then in the column to the right, fill down this formula:

=LEFT(E8,2)&"-"&MID(E8,3,4)&"-"&MID(E8,7,7)&"-"&TEXT(RIGHT(E8,LEN(E8)-13),"000")

I had the numbers starting in E8. Modify accordingly.

Excel 2010
	E	F
8	1212341234567123	12-1234-1234567-123
9	121234123456712	12-1234-1234567-012
Sheet6

Cell Formulas
Range	Formula
F8	=LEFT(E8,2)&"-"&MID(E8,3,4)&"-"&MID(E8,7,7)&"-"&TEXT(RIGHT(E8,LEN(E8)-13),"000")

[TABLE="width: 179"]
<colgroup><col width="179"></colgroup><tbody>[TR]
[TD="class: xl65, width: 179"][/TD]
[/TR]
</tbody>[/TABLE]

 

[tlc53]

Thank you! That does indeed work.
The only issue is, this is a form which users need to complete. It doesn't really work to have them enter the data in one field and then for it to show correctly in another. Ideally, I want it to do everything in the one cell.
However, I'll play around with the formula you provided in data validation and see if I can get it work. Thank you

 

[Rick Rothstein]

The only issue is, this is a form which users need to complete. It doesn't really work to have them enter the data in one field and then for it to show correctly in another. Ideally, I want it to do everything in the one cell.

You could use event code to physically change the user's entry to the shown format you want the values in. I gather from the above statement that you only want to do this to a single cell. If that is correct, consider this event code where I have assumed that single cell to be C3 (change as needed). Note that the cell must be formatted as Text and the number of digits entered must be 16.

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "[B][COLOR="#FF0000"]C3[/COLOR][/B]" Then
    Application.EnableEvents = False
    Target.Value = Format(Replace(Replace(Target.Value, " ", ""), "-", ""), "@@-@@@@-@@@@@@@-@@@")
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[tlc53]

Thanks Rick. I hadn't realised how difficult this would be.
The only issue is, they could enter 15 or 16 numbers. If they enter 15 numbers, then a "0" needs to be entered at position 14 to make it up to 16.
Is this do-able with an Event Code or is it restricted to only one length?

 

[Rick Rothstein]

Thanks Rick. I hadn't realised how difficult this would be.
The only issue is, they could enter 15 or 16 numbers. If they enter 15 numbers, then a "0" needs to be entered at position 14 to make it up to 16.
Is this do-able with an Event Code or is it restricted to only one length?

Okay, give this Event Code a try instead..

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "C3" Then
    Application.EnableEvents = False
    If Len(Target.Value) = 15 Then Target.Value = Application.Replace(Target.Value, 14, 0, "0")
    Target.Value = Format(Replace(Replace(Target.Value, " ", ""), "-", ""), "@@-@@@@-@@@@@@@-@@@")
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]

 

[tlc53]

Thanks for the suggestion but it doesn't return the correct format. For example, it returned 2.-2444-4777777-733E+15
It doesn't appear to like such a large number.

I'm surprised I can't get this working through Data Validation. Is Data Validation able to add symbols/hyphens automatically?

 

[Rick Rothstein]

Thanks for the suggestion but it doesn't return the correct format. For example, it returned 2.-2444-4777777-733E+15

You forgot... the cell must be formatted as Text before you put your number into it. Do that and the code should then work fine for you.

 

[tlc53]

Aha, you're right! It works now!!
There are actually 10 cells altogether which have bank account details to be added.

D12, D68, D124, D180, D236, D292, D348, D404, D460, D516. So basically starting at D12 + 56 rows

I see I can duplicate your code 10x and that works but I was just wondering if there's a shorter way to do it?
There is already a lot going on with this particular spreadsheet and it operates on the slow side, so anything to make it run smoother would be great.

Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
 With Application
.ScreenUpdating = False
.EnableEvents = False
End With


  If Target.Address(0, 0) = "D12" Then
    Application.EnableEvents = False
    If Len(Target.Value) = 15 Then Target.Value = Application.Replace(Target.Value, 14, 0, "0")
    Target.Value = Format(Replace(Replace(Target.Value, " ", ""), "-", ""), "@@-@@@@-@@@@@@@-@@@")
    Application.EnableEvents = True
  End If


If Not Intersect(Target, Range("No._Bank_Accounts")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value




    Case "Please Select"
        Range("26:569").EntireRow.Hidden = True
        
    Case 1
        Range("26:569").EntireRow.Hidden = True
        
    Case 2
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:569").EntireRow.Hidden = True
        
    Case 3
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:569").EntireRow.Hidden = True
        
    Case 4
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:569").EntireRow.Hidden = True
        
    Case 5
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:569").EntireRow.Hidden = True
        
    Case 6
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:289,306:569").EntireRow.Hidden = True
        
    Case 7
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:289,306:345,362:569").EntireRow.Hidden = True
        
    Case 8
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:569").EntireRow.Hidden = True
        
    Case 9
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:457,474:569").EntireRow.Hidden = True
        
    Case 10
        Range("26:569").EntireRow.Hidden = False
        Range("26:65,82:121,138:177,194:233,250:289,306:345,362:401,418:457,474:513,530:569").EntireRow.Hidden = True
        
End Select
End If
 
    If Range("Plus_YN_01") = "NO" Then
        Range("26:45").EntireRow.Hidden = True
    Else
        Range("26:33,44:45").EntireRow.Hidden = False
    End If




    If Range("Less_YN_01") = "NO" Then
        Range("46:65").EntireRow.Hidden = True
    Else
        Range("46:53,64:65").EntireRow.Hidden = False
    End If
      
      
    If Range("Plus_YN_02") = "NO" Then
        Range("82:101").EntireRow.Hidden = True
    Else
        Range("82:90,100:101").EntireRow.Hidden = False
    End If




    If Range("Less_YN_02") = "NO" Then
        Range("102:121").EntireRow.Hidden = True
    Else
        Range("102:109,120:121").EntireRow.Hidden = False
    End If
    
    
        If Range("Plus_YN_03") = "NO" Then
        Range("138:157").EntireRow.Hidden = True
    Else
        Range("138:145,156:157").EntireRow.Hidden = False
    End If




    If Range("Less_YN_03") = "NO" Then
        Range("158:177").EntireRow.Hidden = True
    Else
        Range("158:165,176:177").EntireRow.Hidden = False
    End If
        
    
        If Range("Plus_YN_04") = "NO" Then
        Range("194:213").EntireRow.Hidden = True
    Else
        Range("194:201,212:213").EntireRow.Hidden = False
    End If




    If Range("Less_YN_04") = "NO" Then
        Range("214:233").EntireRow.Hidden = True
    Else
        Range("214:221,232:233").EntireRow.Hidden = False
    End If
            
    
        If Range("Plus_YN_04") = "NO" Then
        Range("194:213").EntireRow.Hidden = True
    Else
        Range("194:201,212:213").EntireRow.Hidden = False
    End If




    If Range("Less_YN_04") = "NO" Then
        Range("214:233").EntireRow.Hidden = True
    Else
        Range("214:221,232:233").EntireRow.Hidden = False
    End If
            
    
        If Range("Plus_YN_05") = "NO" Then
        Range("250:269").EntireRow.Hidden = True
    Else
        Range("250:257,268:269").EntireRow.Hidden = False
    End If




    If Range("Less_YN_05") = "NO" Then
        Range("270:289").EntireRow.Hidden = True
    Else
        Range("270:277,288:289").EntireRow.Hidden = False
    End If
    
    
        If Range("Plus_YN_06") = "NO" Then
        Range("306:325").EntireRow.Hidden = True
    Else
        Range("306:313,324:325").EntireRow.Hidden = False
    End If




    If Range("Less_YN_06") = "NO" Then
        Range("326:345").EntireRow.Hidden = True
    Else
        Range("326:333,344:345").EntireRow.Hidden = False
    End If


    
        If Range("Plus_YN_07") = "NO" Then
        Range("362:381").EntireRow.Hidden = True
    Else
        Range("362:369,380:381").EntireRow.Hidden = False
    End If




    If Range("Less_YN_07") = "NO" Then
        Range("382:401").EntireRow.Hidden = True
    Else
        Range("382:389,400:401").EntireRow.Hidden = False
    End If
    
    
        If Range("Plus_YN_08") = "NO" Then
        Range("418:437").EntireRow.Hidden = True
    Else
        Range("418:425,436:437").EntireRow.Hidden = False
    End If




    If Range("Less_YN_08") = "NO" Then
        Range("438:457").EntireRow.Hidden = True
    Else
        Range("438:445,456:457").EntireRow.Hidden = False
    End If
        
    
        If Range("Plus_YN_09") = "NO" Then
        Range("474:493").EntireRow.Hidden = True
    Else
        Range("474:481,492:493").EntireRow.Hidden = False
    End If




    If Range("Less_YN_09") = "NO" Then
        Range("494:513").EntireRow.Hidden = True
    Else
        Range("494:501,512:513").EntireRow.Hidden = False
    End If
        If Range("Plus_YN_10") = "NO" Then
        Range("530:549").EntireRow.Hidden = True
    Else
        Range("530:537,548:549").EntireRow.Hidden = False
    End If
    If Range("Less_YN_10") = "NO" Then
        Range("550:569").EntireRow.Hidden = True
    Else
        Range("550:557,568:569").EntireRow.Hidden = False
    End If
Dim rng As Range
Set rng = Intersect(Target, [B33:B43,B53:B64,B90:B99,B109:B119,B145:B155,B165:B175,B201:B211,B221:B231,B257:B267,B277:B287,B313:B323,B333:B343,B369:B379,B389:B399,B425:B435,B445:B455,B481:B491,B501:B511,B537:B547,B557:B567])
If Not rng Is Nothing Then rng(2, 1).EntireRow.Hidden = False
 
 With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub