custom format cell with text and numbers
 

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj

custom format cell with text and numbers
 

Well, you could use a formula if AA111122223 never changes the number of
characters.

Assume AA111122223 is in A1.
=concatenate(left(a1,2),"-",mid(a1,4,4),"-",mid(a1,7,4),"-",right(A1,1))

Dave
--
Brevity is the soul of wit.


"maryj" wrote:

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj

custom format cell with text and numbers
 

In a helper cell:

=LEFT(A1,2) & "-" & MID(A1,3,4) & "-" & MID(A1,6,4) & "-" & RIGHT(A1,1)

--
Gary's Student


"maryj" wrote:

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj

custom format cell with text and numbers
 

IN a helper column put this and copy down.........

=LEFT(A1,2)&"-"&MID(A1,3,4)&"-"&MID(A1,6,4)&"-"&RIGHT(A1,1)

Vaya con Dios,
Chuck, CABGx3



"maryj" wrote:

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj

custom format cell with text and numbers
 

If the string always starts with AA and is always 9 characters, you could use a
custom format:

"AA-"0000-0000-0

If the first two letters can vary, but the total string is always 11 characters
(without the dashes), you could use one column for data entry and another cell
to make it look pretty:

=left(a1,2)&"-"&mid(a1,3,4)&"-"&mid(a1,7,4)&"-"&right(a1,1)

You could actually have an event macro that runs and modifies the input as soon
as the user hit enter.

maryj wrote:

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj

--

Dave Peterson

custom format cell with text and numbers
 

Thanks to all.
--
maryj


"Dave F" wrote:

Well, you could use a formula if AA111122223 never changes the number of
characters.

Assume AA111122223 is in A1.
=concatenate(left(a1,2),"-",mid(a1,4,4),"-",mid(a1,7,4),"-",right(A1,1))

Dave
--
Brevity is the soul of wit.


"maryj" wrote:

Client needs to change existing values from AA111122223 to AA-1111-2222-3. Is
there any way to do this with custom formatting? Or what other options might
there be? Thanks!
--
maryj