Is there anyone out there that can tell me how to create a format that does
this:
07-108029827-A it's the A that needs to be a substitue alpa character,
because the A may have to be a B, or C, or D.

Thanks

Is the "number" part always going to be 07-108029827? In other words, what
do you want to TYPE into the cell and what do you want appear? If the number
part is not the same all the time, where is it coming from?

Rick

"Rick" <(E-Mail Removed)> wrote in message
news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
> Is there anyone out there that can tell me how to create a format that
> does
> this:
> 07-108029827-A it's the A that needs to be a substitue alpa character,
> because the A may have to be a B, or C, or D.
>
> Thanks

The number part as well as the alpha part can be anynumber and any alpha. It
is a trace code for medical billing, but the format will always be the same.

"Rick Rothstein (MVP - VB)" wrote:

> Is the "number" part always going to be 07-108029827? In other words, what
> do you want to TYPE into the cell and what do you want appear? If the number
> part is not the same all the time, where is it coming from?
>
> Rick
>
> "Rick" <(E-Mail Removed)> wrote in message
> news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
> > Is there anyone out there that can tell me how to create a format that
> > does
> > this:
> > 07-108029827-A it's the A that needs to be a substitue alpa character,
> > because the A may have to be a B, or C, or D.
> >
> > Thanks
>
>

Then I guess I am not following what you want or need. What are you
physically going to type into the cell you asked to be able to format? Where
does the A, B, C, D you asked about come from? You are going to have to give
us a little more detail about your layout, what is typed where, what you
want to look like what where, etc.

Rick


"Rick" <(E-Mail Removed)> wrote in message
news:895F4678-B59A-4766-97A8-(E-Mail Removed)...
> The number part as well as the alpha part can be anynumber and any alpha.
> It
> is a trace code for medical billing, but the format will always be the
> same.
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> Is the "number" part always going to be 07-108029827? In other words,
>> what
>> do you want to TYPE into the cell and what do you want appear? If the
>> number
>> part is not the same all the time, where is it coming from?
>>
>> Rick
>>
>> "Rick" <(E-Mail Removed)> wrote in message
>> news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
>> > Is there anyone out there that can tell me how to create a format that
>> > does
>> > this:
>> > 07-108029827-A it's the A that needs to be a substitue alpa
>> > character,
>> > because the A may have to be a B, or C, or D.
>> >
>> > Thanks
>>
>>

Try and describe what you have and what you have want to achieve.

Regards,
Peter T

"Rick" <(E-Mail Removed)> wrote in message
news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
> Is there anyone out there that can tell me how to create a format that
does
> this:
> 07-108029827-A it's the A that needs to be a substitue alpa character,
> because the A may have to be a B, or C, or D.
>
> Thanks

Rick:
I will be typing the entire number and letter in the cell, I have the first
part of the format which is 00-000000000-? the question mark is what is
missing. what character do I use to have a single alpha appear in that
position.

"Rick Rothstein (MVP - VB)" wrote:

> Then I guess I am not following what you want or need. What are you
> physically going to type into the cell you asked to be able to format? Where
> does the A, B, C, D you asked about come from? You are going to have to give
> us a little more detail about your layout, what is typed where, what you
> want to look like what where, etc.
>
> Rick
>
>
> "Rick" <(E-Mail Removed)> wrote in message
> news:895F4678-B59A-4766-97A8-(E-Mail Removed)...
> > The number part as well as the alpha part can be anynumber and any alpha.
> > It
> > is a trace code for medical billing, but the format will always be the
> > same.
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> Is the "number" part always going to be 07-108029827? In other words,
> >> what
> >> do you want to TYPE into the cell and what do you want appear? If the
> >> number
> >> part is not the same all the time, where is it coming from?
> >>
> >> Rick
> >>
> >> "Rick" <(E-Mail Removed)> wrote in message
> >> news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
> >> > Is there anyone out there that can tell me how to create a format that
> >> > does
> >> > this:
> >> > 07-108029827-A it's the A that needs to be a substitue alpa
> >> > character,
> >> > because the A may have to be a B, or C, or D.
> >> >
> >> > Thanks
> >>
> >>
>
>

To Rick & Peter:
I have a cell that is for entry by keyboard. It will contain a billing
reference number, that has a format of 2digits, a dash, 9digits, a dash, and
a alpha character a thru z. All I want to do is enter that code as
follows:12123456789a, and have it show in the cell as 12-123456789-A, or
12-123456789-B, or 12-123456789-Z

"Peter T" wrote:

> Try and describe what you have and what you have want to achieve.
>
> Regards,
> Peter T
>
> "Rick" <(E-Mail Removed)> wrote in message
> news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
> > Is there anyone out there that can tell me how to create a format that
> does
> > this:
> > 07-108029827-A it's the A that needs to be a substitue alpa character,
> > because the A may have to be a B, or C, or D.
> >
> > Thanks
>
>
>

On Sun, 9 Mar 2008 11:22:00 -0700, Rick <(E-Mail Removed)> wrote:

>Is there anyone out there that can tell me how to create a format that does
>this:
> 07-108029827-A it's the A that needs to be a substitue alpa character,
>because the A may have to be a B, or C, or D.
>
>Thanks

You can't have a variable letter character as part of a cell number format,
without using a VBA Macro to actually modify the cell format.

If you are typing the characters into some cell, all at once with no spaces or
hyphens, you could use a formula in an adjacent cell to have it look the way
you want.

=TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1)

You could even do some validation:

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISTEXT(RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

--ron

Ah, now I see what you want. Okay, I don't think you can do this with a cell
format, but you can do it with a worksheet event procedure. Right-click the
tab for the sheet where you want this functionality to be on and copy/paste
this code into the code window that appears....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = Format(UCase(Target.Value), "&&-&&&&&&&&&-&")
Whoops:
Application.EnableEvents = True
End Sub

Note that I set this up for Column D (that is what the '<> 4' test is doing)
but you can change this as needed. Now, go back to your sheet and type in
one of your numbers into a cell in Column D (don't type in any dashes, the
code will insert them for your)... hit Enter, Tab or click into a different
cell and watch what happens to your entry.

Rick


"Rick" <(E-Mail Removed)> wrote in message
news:6485D04E-1368-4E16-B066-(E-Mail Removed)...
> Rick:
> I will be typing the entire number and letter in the cell, I have the
> first
> part of the format which is 00-000000000-? the question mark is what is
> missing. what character do I use to have a single alpha appear in that
> position.
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> Then I guess I am not following what you want or need. What are you
>> physically going to type into the cell you asked to be able to format?
>> Where
>> does the A, B, C, D you asked about come from? You are going to have to
>> give
>> us a little more detail about your layout, what is typed where, what you
>> want to look like what where, etc.
>>
>> Rick
>>
>>
>> "Rick" <(E-Mail Removed)> wrote in message
>> news:895F4678-B59A-4766-97A8-(E-Mail Removed)...
>> > The number part as well as the alpha part can be anynumber and any
>> > alpha.
>> > It
>> > is a trace code for medical billing, but the format will always be the
>> > same.
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >
>> >> Is the "number" part always going to be 07-108029827? In other words,
>> >> what
>> >> do you want to TYPE into the cell and what do you want appear? If the
>> >> number
>> >> part is not the same all the time, where is it coming from?
>> >>
>> >> Rick
>> >>
>> >> "Rick" <(E-Mail Removed)> wrote in message
>> >> news:6BCBC2A7-3573-46E3-815E-(E-Mail Removed)...
>> >> > Is there anyone out there that can tell me how to create a format
>> >> > that
>> >> > does
>> >> > this:
>> >> > 07-108029827-A it's the A that needs to be a substitue alpa
>> >> > character,
>> >> > because the A may have to be a B, or C, or D.
>> >> >
>> >> > Thanks
>> >>
>> >>
>>
>>

On Sun, 09 Mar 2008 16:19:57 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>=IF(AND(ISNUMBER(-LEFT(A1,11)),ISTEXT(RIGHT(A1,1)),LEN(A1)=12),
>TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

Better with:

=IF(AND(ISNUMBER(-LEFT(A1,11)),ISERR(-RIGHT(A1,1)),LEN(A1)=12),
TEXT(LEFT(A1,11),"00-000000000-")& RIGHT(A1,1),"invalid entry")

--ron