Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 15th 08, 07:18 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 54
Default 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   Report Post  
Old January 15th 08, 07:36 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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   Report Post  
Old January 15th 08, 08:27 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old January 16th 08, 01:25 AM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 54
Default 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   Report Post  
Old January 16th 08, 05:38 AM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old July 8th 08, 05:21 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 1
Default 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   Report Post  
Old July 8th 08, 05:22 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 2
Default 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   Report Post  
Old July 8th 08, 05:29 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format cell with 18 characters Format cell with 18 characters Excel Discussion (Misc queries) 1 August 15th 07 06:36 PM
Custom Cell format to mimic time format [email protected] Excel Discussion (Misc queries) 6 November 7th 06 09:17 PM
custom number and random text format CAD Teacher Excel Discussion (Misc queries) 0 June 21st 06 11:56 AM
How can I generate random characters in Excel Tayo Excel Discussion (Misc queries) 5 April 7th 06 02:07 PM
Format a cell with a custom number format Armor Excel Worksheet Functions 4 January 29th 06 11:25 PM


All times are GMT +1. The time now is 05:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017