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