How to Insert Dashes

[childofthe1980s]

Hello:

In various cells, I have such data as "1000001p" How can I have Excel
automatically change it to "1000-001P" or even "1000-001-P"?

You see, I need dashes inserted and I need the last alpha character to be
capitalized.

Thanks!

childofthe1980s

[Don Guillett]

Have a look in HELP index for MID

--
Don Guillett
SalesAid Software
[email protected]
"childofthe1980s" <[email protected]> wrote in
message news:[email protected]...
> Hello:
>
> In various cells, I have such data as "1000001p" How can I have Excel
> automatically change it to "1000-001P" or even "1000-001-P"?
>
> You see, I need dashes inserted and I need the last alpha character to be
> capitalized.
>
> Thanks!
>
> childofthe1980s

[Toppers]

Hi,
Assuming data in A1, put the following in B1:

=TEXT(LEFT(A1,LEN(A1)-1),"0000-000-") & UPPER(RIGHT(A1,1))

Change the "0000-000-" to suit your needs.

HTH


"childofthe1980s" wrote:

> Hello:
>
> In various cells, I have such data as "1000001p" How can I have Excel
> automatically change it to "1000-001P" or even "1000-001-P"?
>
> You see, I need dashes inserted and I need the last alpha character to be
> capitalized.
>
> Thanks!
>
> childofthe1980s

[Jim Thomlinson]

Here is a formula. It assumes that 10000001p is in Cell A1

=LEFT(A1,5)&"-"&MID(A1,6,3)&"-"&UPPER(RIGHT(A1,1))

--
HTH...

Jim Thomlinson


"childofthe1980s" wrote:

> Hello:
>
> In various cells, I have such data as "1000001p" How can I have Excel
> automatically change it to "1000-001P" or even "1000-001-P"?
>
> You see, I need dashes inserted and I need the last alpha character to be
> capitalized.
>
> Thanks!
>
> childofthe1980s