Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format using random alphabetic characters
Hi all,
I want to create a custom cell format that allows for the following: When someone types five digits (could be alphabetic or numeric or both), automatically add "MCY ID" before it. So for example: if you type: 29H73, it'll automatically change to "MCY ID 29H73" if you type: 9992A, it'll automatically change to "MCY ID 9992A" if you type AA76P, it'll automatically change to "MCY ID AA76P" ETC. Is this possible? I'm familiar with Custom cell formatting, but can't figure out how to do that specifically. It's the letters that are messing me up. Thanks! Joe |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format using random alphabetic characters
Maybe using autocorrect would be sufficient.
Tools|Autocorrect options|Autocorrect tab replace: 29H73 with: MCY ID 29H73 (and the other stuff, too) This is specific to each user, though. And the autocorrect list is also shared by the other programs in the office suite (MSWord, PPT, ...) An alternative maybe to just do the normal data entry, then when you're done, just do a few Edit|Replaces. Access Joe wrote: Hi all, I want to create a custom cell format that allows for the following: When someone types five digits (could be alphabetic or numeric or both), automatically add "MCY ID" before it. So for example: if you type: 29H73, it'll automatically change to "MCY ID 29H73" if you type: 9992A, it'll automatically change to "MCY ID 9992A" if you type AA76P, it'll automatically change to "MCY ID AA76P" ETC. Is this possible? I'm familiar with Custom cell formatting, but can't figure out how to do that specifically. It's the letters that are messing me up. Thanks! Joe -- Dave Peterson |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format using random alphabetic characters
You could use a helper column with a formula.
=IF(LEN(A1)< 5, "","MCY ID " & A1) Or you could use event code to change in place as you type and enter. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A100" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If Len(cell.Value) < 5 Then GoTo ws_exit cell.Value = "MCY ID " & cell.Value Next cell End If ws_exit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 11:18:03 -0800, Access Joe wrote: Hi all, I want to create a custom cell format that allows for the following: When someone types five digits (could be alphabetic or numeric or both), automatically add "MCY ID" before it. So for example: if you type: 29H73, it'll automatically change to "MCY ID 29H73" if you type: 9992A, it'll automatically change to "MCY ID 9992A" if you type AA76P, it'll automatically change to "MCY ID AA76P" ETC. Is this possible? I'm familiar with Custom cell formatting, but can't figure out how to do that specifically. It's the letters that are messing me up. Thanks! Joe |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format using random alphabetic characters
Yeah - I'm familiar with all the tools you guys mention. But this really
isn't what the client wants. Nothing in custom formatting, huh? Thanks anyway for the responses. I appreciate it. If anyone else knows of a way using the Custom Formatting box, feel free to let me know. Thanks again! "Gord Dibben" wrote: You could use a helper column with a formula. =IF(LEN(A1)< 5, "","MCY ID " & A1) Or you could use event code to change in place as you type and enter. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A100" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If Len(cell.Value) < 5 Then GoTo ws_exit cell.Value = "MCY ID " & cell.Value Next cell End If ws_exit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 11:18:03 -0800, Access Joe wrote: Hi all, I want to create a custom cell format that allows for the following: When someone types five digits (could be alphabetic or numeric or both), automatically add "MCY ID" before it. So for example: if you type: 29H73, it'll automatically change to "MCY ID 29H73" if you type: 9992A, it'll automatically change to "MCY ID 9992A" if you type AA76P, it'll automatically change to "MCY ID AA76P" ETC. Is this possible? I'm familiar with Custom cell formatting, but can't figure out how to do that specifically. It's the letters that are messing me up. Thanks! Joe |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format using random alphabetic characters
Custom Formatting works only on numerics, not on text.
You can custom format a number like such "The amount is "#,##0" dollars" but no way to custom format alpha/numeric mix AFAIK Gord On Tue, 15 Jan 2008 17:25:00 -0800, Access Joe wrote: Yeah - I'm familiar with all the tools you guys mention. But this really isn't what the client wants. Nothing in custom formatting, huh? Thanks anyway for the responses. I appreciate it. If anyone else knows of a way using the Custom Formatting box, feel free to let me know. Thanks again! "Gord Dibben" wrote: You could use a helper column with a formula. =IF(LEN(A1)< 5, "","MCY ID " & A1) Or you could use event code to change in place as you type and enter. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A100" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If Len(cell.Value) < 5 Then GoTo ws_exit cell.Value = "MCY ID " & cell.Value Next cell End If ws_exit: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 11:18:03 -0800, Access Joe wrote: Hi all, I want to create a custom cell format that allows for the following: When someone types five digits (could be alphabetic or numeric or both), automatically add "MCY ID" before it. So for example: if you type: 29H73, it'll automatically change to "MCY ID 29H73" if you type: 9992A, it'll automatically change to "MCY ID 9992A" if you type AA76P, it'll automatically change to "MCY ID AA76P" ETC. Is this possible? I'm familiar with Custom cell formatting, but can't figure out how to do that specifically. It's the letters that are messing me up. Thanks! Joe |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
Custom cell format
Hey, it's pretty easy to do.
In the custom format, just type: "MCY ID "#;"MCY ID "#;"MCY ID 00000";"MCY ID "@ Numbers are taken care of by the first 3 parts, and alphanumeric entries are done by the last (whatever text is entered in the cell will be placed where the "@" is). This doesn't make sure that only 5 characters are entered, but it's simple and easy to create. |
#7
Posted to microsoft.public.excel.setup
|
|||
|
|||
That didn't come out
Hey, it's pretty easy to do.
In the custom format, just type: "MCY ID "#;"MCY ID "#;"MCY ID 00000";"MCY ID "@ Numbers are taken care of by the first 3 parts, and alphanumeric entries are done by the last (whatever text is entered in the cell will be placed where the "@" is). This doesn't make sure that only 5 characters are entered, but it is simple and easy to create. |
#8
Posted to microsoft.public.excel.setup
|
|||
|
|||
still won't show
"MCY ID "#;"MCY ID "#;"MCY ID 00000";"MCY ID "@
^^If that doesn't work, what you need to enter is (quote)MCY ID (endquote)(pound sign)(colon) (quote)MCY ID (endquote)(pound sign)(colon) (quote)MCY ID 00000(endquote)(colon) (quote)MCY ID (endquote)(at sign for email) and it should work |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cell with 18 characters | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
custom number and random text format | Excel Discussion (Misc queries) | |||
How can I generate random characters in Excel | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions |