 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Formatting UK National Insurance Numbers
I want to be able to type in UK NI numbers as say EG123456XZ and have them formatted in a cell so that they appear as EG 12 34 56 XZ Can anyone help with a simple formatting to do this? The XZ makes the *number* text and you cannot format text like you can numbers. You could have an event macro run and change the cell entry. Right-click on the sheet tab and enter this code in the Sheet Module: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim x As Integer Dim NewValue As String If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub For x = 1 To Len(Target.Value) Step 2 NewValue = NewValue + Mid(Target.Value, x, 2) + " " Next x NewValue = Left(NewValue, Len(NewValue) - 1) Application.EnableEvents = False Target.Value = NewValue Application.EnableEvents = True End Sub  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I want to be able to type in UK NI numbers as say EG123456XZ and have > them formatted in a cell so that they appear as EG 12 34 56 XZ > > Can anyone help with a simple formatting to do this? The VBA Format function allow for more compact coding.... Private Sub Worksheet_Change(ByVal Target As Range) If InStr(Target.Value, " ") = 0 And Not _ Intersect(Target, Range("D:D")) Is Nothing Then Application.EnableEvents = False Target.Value = Format$(Target.Value, "@@ @@ @@ @@ ") Application.EnableEvents = True End If End Sub Rick > The XZ makes the *number* text and you cannot format text like you can > numbers. You could have an event macro run and change the cell entry. [quoted text clipped - 22 lines] >> >> Can anyone help with a simple formatting to do this? As a matter of fact, in thinking about it, we can remove the EnableEvents calls since I filter on an included blank space, the Change event won't run anything on the second call back to it... Private Sub Worksheet_Change(ByVal Target As Range) If InStr(Target.Value, " ") = 0 And Not _ Intersect(Target, Range("D:D")) Is Nothing Then Target.Value = Format$(Target.Value, "@@ @@ @@ @@ ") End If End Sub Rick > The VBA Format function allow for more compact coding.... > [quoted text clipped - 35 lines] >>> >>> Can anyone help with a simple formatting to do this? Thank you Rick - I learned a bit more. I did not realise that @ in Format > Cell > Number refers to the whole cell entry but in VBA it refers to only one character. Also, is there a reason for the trailing space in the format? It is not like you to have surplus characters <g> SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > The VBA Format function allow for more compact coding.... > [quoted text clipped - 35 lines] >>> >>> Can anyone help with a simple formatting to do this? > > The VBA Format function allow for more compact coding.... > > [quoted text clipped - 10 lines] > > Cell > Number refers to the whole cell entry but in VBA it refers to > only one character. Yes, there is a big differecnce between how the spreadsheet's FORMAT function and VBA's Format function perform; and for me, that difference is annoyingly large. In the VBA world, each @ symbol stands for one characters from the text in the first argument; other characters appearing in the pattern string, with a few exceptions, simply get placed in the output string where shown (in this case, the blank spaces). > Also, is there a reason for the trailing space in the format? > It is not like you to have surplus characters <g> <g> Nope, I don't like surplus characters... and I haven't include any this time either. Before I can tell you why the space is not surplus, I have to explain the way the @ symbols get used in the pattern string. There are two ways to fill the format pattern string when using the @ symbol... right-to-left and, of course, left-to-right. But, believe it or not, the two methods do not work the same way when it comes to a string longer than the number of @ symbols. First, the normal method of filling in the @ symbols with characters from the first argument is from right to left. This is usually used to right-justify text in a column. If you start the pattern string off with an exclamation point (!), then the @ symbols are filled in from left to right. Now, the difference... if you use the exclamation point in front of the pattern string, and if your text is longer than the number @ signs to be filled in, any excess characters are truncated away and not displayed. However, for the non-exclamation pattern string, characters fill from the right with each newly added character pushing the previous character over one @ symbol position to the left to make room for it in the right-most @ symbol's position. This goes on until the last @ symbol has been filled in. At this point, if the text contains more characters than @ symbols, they are NOT truncated away... they are simply concatenated on the end of the pattern string. Okay, armed with this explanation, notice I did not use an exclamation point in front of the pattern string AND that I provided a pattern string with only 8 @ symbols in it even though I know there text string will be more than 8 characters long. Doing this guarantees the excess characters will be concatenated after the pattern string. The trailing blank space is the one used to separate the trailing letters from the last digit in the inputted text string. Rick Thank you for the very full explanation Rick, I have read it through, (several times), and I see what you are saying but I find the following in XL97: > filled in from left to right. Now, the difference... if you use the > exclamation point in front of the pattern string, and if your text is > longer than the number @ signs to be filled in, any excess characters are > truncated away and not displayed. With an entry of 1234567890AB and the VBA Format of "!@@ @@ @@ @@ " I get 12 34 56 78 90AB ie the *extra* characters are concatenated at the end not truncated. Also your If statement: If InStr(Target.Value, " ") = 0 And Not _ Intersect(Target, Range("D:D")) Is Nothing Then causes a Run Time Error 13 Type Mismatch whenever a multy-cell range is actioned including dragging formulas and deleting ranges. I don't know if it is different in other versions. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> > The VBA Format function allow for more compact coding.... >> > [quoted text clipped - 52 lines] > > Rick Hmm! I gather you are using XL97. I am using XL2003 and I also have XL2007 installed too... the description I gave for the VBA Format function reflects what I see in both of my versions of Excel. For example, when I run this code in the Immediate window inside the VBA editor... Value = "12345678ABCD" ? "<" & Format(Value,"@@ @@ @@ @@ ") & ">" ? "<" & Format(Value,"!@@ @@ @@ @@ ") & ">" I get these two results printed out... 1st Statement: 12 34 56 78 ABCD 2nd Statement: 56 78 AB CD You are getting something different when you execute them? I was under the impression that XL97 on upwards all used VBA Version 6 for their macro languages... am I mistaken? As for the code problem you pointed out, thanks for bringing it to my attention; I think this fixes it... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If InStr(Target.Value, " ") = 0 And _ Not Intersect(Target, Range("D:D")) Is Nothing Then Target.Value = Format$(Target.Value, "@@ @@ @@ @@ ") End If End If End Sub Rick > Thank you for the very full explanation Rick, I have read it through, > (several times), and I see what you are saying but I find the following in [quoted text clipped - 78 lines] >> >> Rick Hi Rick, If I run this Macro: Sub test() PValue = "12345678ABCD" Debug.Print "<" & Format(PValue, "@@ @@ @@ @@ ") & ">" Debug.Print "<" & Format(PValue, "!@@ @@ @@ @@ ") & ">" End Sub I get: <123456 78 AB CD > <12 34 56 78 ABCD> Returned in the immediate window so I assume that there is a difference in the versions. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hmm! I gather you are using XL97. I am using XL2003 and I also have XL2007 > installed too... the description I gave for the VBA Format function [quoted text clipped - 111 lines] >>> >>> Rick > If I run this Macro: > [quoted text clipped - 8 lines] > <123456 78 AB CD > > <12 34 56 78 ABCD> And I get this... <12 34 56 78 ABCD> <56 78 AB CD > Am I wrong in thinking XL97 has VBA Version 6 at its heart? If you do a Help/About from the VBA editor's menu bar, what version of VB is reported? Rick > Am I wrong in thinking XL97 has VBA Version 6 at its heart? If you do a > Help/About from the VBA editor's menu bar, what version of VB is reported? It doesn't say I just get a dialog box reporting: Microsoft Visual Basic Copyright (c) 1987 - 1996 Microsoft Corp SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >> If I run this Macro: >> [quoted text clipped - 18 lines] > > Rick One way is to use another cell with a formula that will format it the way you like: =left(a1,2)&" "&mid(a1,3,2)&" "&mid(a1,5,2)&" "&right(a1,2) > I want to be able to type in UK NI numbers as say EG123456XZ and have > them formatted in a cell so that they appear as EG 12 34 56 XZ > > Can anyone help with a simple formatting to do this? SignatureDave Peterson Dave can you contact me (gary_f_shelton@msn.com) I need help with the code you wrote back in 2004 regarding ...fill blank cells in column with value above... Thanks, GFS SignatureGS > One way is to use another cell with a formula that will format it the way you > like: [quoted text clipped - 5 lines] > > > > Can anyone help with a simple formatting to do this? Please post your question in the newsgroup. Make sure you give details. > Dave can you contact me (gary_f_shelton@msn.com) I need help with the code > you wrote back in 2004 regarding ...fill blank cells in column with value [quoted text clipped - 18 lines] > > > > Dave Peterson SignatureDave Peterson Hi Using another column, and assuming your data to be in column A, then =LEFT(A1,2)&" "&TEXT(MID(A1,3,6),"00 00 00")&" "&RIGHT(A1,LEN(A1)-8) I am not aware of NI numbers having 2 alpha characters at the end - mine and my wife's certainly don't, they have a single alpha character. AA nn nn nn A The above formula will deal with either case. Copy down as far as required. SignatureRegards Roger Govier > I want to be able to type in UK NI numbers as say EG123456XZ and have > them formatted in a cell so that they appear as EG 12 34 56 XZ > > Can anyone help with a simple formatting to do this? > Using another column, and assuming your data to be in column A, then > =LEFT(A1,2)&" "&TEXT(MID(A1,3,6),"00 00 00")&" "&RIGHT(A1,LEN(A1)-8) We can shave off some 14 characters plus a function call by moving those external spaces inside the TEXT function pattern string and using the MID function in place of RIGHT function (assuming the entries are "near-normal" looking, that is)... =LEFT(J1,2)&TEXT(MID(J1,3,6)," 00 00 00 ")&MID(J1,9,9) Rick Nice modification, Rick. SignatureRegards Roger Govier >> Using another column, and assuming your data to be in column A, then >> =LEFT(A1,2)&" "&TEXT(MID(A1,3,6),"00 00 00")&" "&RIGHT(A1,LEN(A1)-8) [quoted text clipped - 7 lines] > > Rick In microsoft.public.excel on Fri, 28 Dec 2007, Roger Govier <roger@technology4unospamdotcodotuk.?.invalid> wrote : >Hi > [quoted text clipped - 4 lines] >mine and my wife's certainly don't, they have a single alpha character. >AA nn nn nn A And the last character can only be A, B, C or D, IIRC. SignaturePaul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.