| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting text and numbers in a cell
I am using Excel 2000
I use a reference which is set up as 2 alpha, 6 numerics and one alpha (aannnnnna). It is the National Insurance (Social Security) Number given to everyone at age 16. I wanted to use a format to ensure that the item was being entered properly and I would also like it to be displayed as 'aa nn nn nn a' (the normal method of notation). I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @' neither of which worked, nor could I find any formats of this type. Is there a way of setting up a custom format that will a format of this type. May I thank you in advance for your help. Sean Bishop |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting text and numbers in a cell
Number Formatting only works with numbers.
But you could use an event macro that does the work for you. It'll actually change the value of the string so that it includes those space characters. If you want to try, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window that just opened: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myStr As String With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler: If Len(.Value) = 9 Then 'do it '@@ ## ## ## @ myStr = Left(.Value, 2) & " " _ & Mid(.Value, 3, 2) & " " _ & Mid(.Value, 5, 2) & " " _ & Mid(.Value, 7, 2) & " " _ & Left(.Value, 1) 'make those letters uppercase??? myStr = UCase(myStr) Application.EnableEvents = False .Value = myStr End If End With ErrHandler: Application.EnableEvents = True End Sub This code modifies any entry in column A. Change this line to look at the range you want: If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then And delete that UCase() line if you don't want it automatically changed to upper case. Some references: David McRitchie's intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm You can read more about events at: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm Sean Bishop wrote: I am using Excel 2000 I use a reference which is set up as 2 alpha, 6 numerics and one alpha (aannnnnna). It is the National Insurance (Social Security) Number given to everyone at age 16. I wanted to use a format to ensure that the item was being entered properly and I would also like it to be displayed as 'aa nn nn nn a' (the normal method of notation). I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @' neither of which worked, nor could I find any formats of this type. Is there a way of setting up a custom format that will a format of this type. May I thank you in advance for your help. Sean Bishop -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting text and numbers in a cell
=LEFT(A1,2)&" "&MID(A1,3,2)&" "&MID(A1,5,2)&" "&MID(A1,7,2)&" "&RIGHT(A1,1)
"Sean Bishop" wrote: I am using Excel 2000 I use a reference which is set up as 2 alpha, 6 numerics and one alpha (aannnnnna). It is the National Insurance (Social Security) Number given to everyone at age 16. I wanted to use a format to ensure that the item was being entered properly and I would also like it to be displayed as 'aa nn nn nn a' (the normal method of notation). I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @' neither of which worked, nor could I find any formats of this type. Is there a way of setting up a custom format that will a format of this type. May I thank you in advance for your help. Sean Bishop |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting text and numbers in a cell
Dave Peterson,
Thank you for your response to my question. It worked and did exactly what I wanted it to do. Typically it is only when you see the answer that you realise that you did not phrase the question fully. I should have said that the spreadsheet is laid out like a form and the question applied to one specific cell. However I now have something I can work upon and also a template if I have any further formatting problems. Again, thank you. Sean Bishop "Dave Peterson" wrote: Number Formatting only works with numbers. But you could use an event macro that does the work for you. It'll actually change the value of the string so that it includes those space characters. If you want to try, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window that just opened: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myStr As String With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler: If Len(.Value) = 9 Then 'do it '@@ ## ## ## @ myStr = Left(.Value, 2) & " " _ & Mid(.Value, 3, 2) & " " _ & Mid(.Value, 5, 2) & " " _ & Mid(.Value, 7, 2) & " " _ & Left(.Value, 1) 'make those letters uppercase??? myStr = UCase(myStr) Application.EnableEvents = False .Value = myStr End If End With ErrHandler: Application.EnableEvents = True End Sub This code modifies any entry in column A. Change this line to look at the range you want: If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then And delete that UCase() line if you don't want it automatically changed to upper case. Some references: David McRitchie's intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm You can read more about events at: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm Sean Bishop wrote: I am using Excel 2000 I use a reference which is set up as 2 alpha, 6 numerics and one alpha (aannnnnna). It is the National Insurance (Social Security) Number given to everyone at age 16. I wanted to use a format to ensure that the item was being entered properly and I would also like it to be displayed as 'aa nn nn nn a' (the normal method of notation). I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @' neither of which worked, nor could I find any formats of this type. Is there a way of setting up a custom format that will a format of this type. May I thank you in advance for your help. Sean Bishop -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatting text and numbers in a cell
If it's a single cell, you can change this line:
If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then to something like: If Intersect(.Cells, Me.Range("x99")) Is Nothing Then Sean Bishop wrote: Dave Peterson, Thank you for your response to my question. It worked and did exactly what I wanted it to do. Typically it is only when you see the answer that you realise that you did not phrase the question fully. I should have said that the spreadsheet is laid out like a form and the question applied to one specific cell. However I now have something I can work upon and also a template if I have any further formatting problems. Again, thank you. Sean Bishop "Dave Peterson" wrote: Number Formatting only works with numbers. But you could use an event macro that does the work for you. It'll actually change the value of the string so that it includes those space characters. If you want to try, rightclick on the worksheet tab that should have this behavior. Select view code and paste this into the code window that just opened: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myStr As String With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler: If Len(.Value) = 9 Then 'do it '@@ ## ## ## @ myStr = Left(.Value, 2) & " " _ & Mid(.Value, 3, 2) & " " _ & Mid(.Value, 5, 2) & " " _ & Mid(.Value, 7, 2) & " " _ & Left(.Value, 1) 'make those letters uppercase??? myStr = UCase(myStr) Application.EnableEvents = False .Value = myStr End If End With ErrHandler: Application.EnableEvents = True End Sub This code modifies any entry in column A. Change this line to look at the range you want: If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then And delete that UCase() line if you don't want it automatically changed to upper case. Some references: David McRitchie's intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm You can read more about events at: Chip Pearson's site: http://www.cpearson.com/excel/events.htm David McRitchie's site: http://www.mvps.org/dmcritchie/excel/event.htm Sean Bishop wrote: I am using Excel 2000 I use a reference which is set up as 2 alpha, 6 numerics and one alpha (aannnnnna). It is the National Insurance (Social Security) Number given to everyone at age 16. I wanted to use a format to ensure that the item was being entered properly and I would also like it to be displayed as 'aa nn nn nn a' (the normal method of notation). I tried setting up custom formats '@@ ## ## ## @' and '@@ @@ @@ @@ @' neither of which worked, nor could I find any formats of this type. Is there a way of setting up a custom format that will a format of this type. May I thank you in advance for your help. Sean Bishop -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formatting cells with numbers as text | Excel Discussion (Misc queries) | |||
| help with formatting a sheet with text and numbers | Excel Worksheet Functions | |||
| Formatting from text to numbers | Excel Worksheet Functions | |||
| numbers to text with formatting | Excel Discussion (Misc queries) | |||
| conditional formatting with numbers and text | Excel Worksheet Functions | |||
Linear Mode
