| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom Format Cell | Excel Discussion (Misc queries) | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| Change CSV-load cell format from GENERAL to TEXT for numbers? | Excel Discussion (Misc queries) | |||
| Format a cell with numbers and user defined text | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
