I would like to use a custom format that will take a number/text string like:

1405828AB

And convert it to:

140N-58W-28-AB

Where the N and W are fixed, but the "AB" can be any combination of two of
the following four letters A, B, C, D.

I have a version that works for everything but the letters at the end and it
is:

###"N"-##"W"-##-

but can't get the rest of it.

Your help is greatly appreciated...

Thanks,
J

You can format numbers--not text.

Maybe you could use two columns--one for your entry and one to hold a formula
that formats the entry nicely.

=text(left(a1,7),"000\N\-00\W\-00\-")&right(a1,2)

And you'd type the entry in A1 like:
01234567AB

J wrote:

I would like to use a custom format that will take a number/text string like:

1405828AB

And convert it to:

140N-58W-28-AB

Where the N and W are fixed, but the "AB" can be any combination of two of
the following four letters A, B, C, D.

I have a version that works for everything but the letters at the end and it
is:

###"N"-##"W"-##-

but can't get the rest of it.

Your help is greatly appreciated...

Thanks,
J

--

Dave Peterson

Thanks!
J

"Dave Peterson" wrote:

You can format numbers--not text.

Maybe you could use two columns--one for your entry and one to hold a formula
that formats the entry nicely.

=text(left(a1,7),"000\N\-00\W\-00\-")&right(a1,2)

And you'd type the entry in A1 like:
01234567AB

J wrote:

I would like to use a custom format that will take a number/text string like:

1405828AB

And convert it to:

140N-58W-28-AB

Where the N and W are fixed, but the "AB" can be any combination of two of
the following four letters A, B, C, D.

I have a version that works for everything but the letters at the end and it
is:

###"N"-##"W"-##-

but can't get the rest of it.

Your help is greatly appreciated...

Thanks,
J

--

Dave Peterson