Greetings,

A while back I was looking to change the cell value formatted as
"000xxx00" to be "Map 000X <XX-00" this finale formatted value to be
copied in this new format to the CustInfo record sheet. After much
discussion, a solution was achieved.

But now I need to keep the original (000xxx00) format and only view
the custom format in both a cell and a UserForm TextBox.

For the sheet cell format, I tried these custom formats in the format
number menu:

"Map" ???X "<" XX-??""
"Map" #### "<" ##-##""
"Map "&####&" <"&##&"-"&##&""
"Map " & #### & " <" & ## & "-" & ## & ""
"Map "####" <"##"-"##""
"Map" #### <##-##
"!Map "0000 "\<"00-00"\"
"!Map @@@@ \<@@-@@\"

None of these work.

One other thing, the cell gets it's value from either this Offset
formula:

=IF(OFFSET(clStart,pfDisc,23)=0,"",OFFSET(clStart, pfDisc,23))

clStart is the reference row (A1 on CustList).
pfDisc is the row in the CustList sheet to be looked at.
23 is the column on that row where to data to be displayed is sitting.
Or direct entry in the edit mode which happens when this cell is
copied and pasted over itself with EditPaste specialValues, edited
copied back over to the CustList sheet as a new record and then the
formula is pasted back into the cell, There is some protection turned
off, on, off and back on also in the mix, but that should not be a
factor (I hope).

Anyone have any ideas to how to see Map 123A <BC-45 in a cell with
the cell value of 123abc45?

Any help, suggestions or direction would be appreciated.

-Minitman

If you don't mind it being in another column, you could do this:

="Map "&UPPER(LEFT(A2,4))&" <"&UPPER(MID(A2,5,2))&"-"&RIGHT(A2,2)&""

assuming 123abc45 is in A2. You could hide column A if it is the result you
want to see.

Hope this helps.

Pete

"Minitman" wrote in message
...
Greetings,

A while back I was looking to change the cell value formatted as
"000xxx00" to be "Map 000X <XX-00" this finale formatted value to be
copied in this new format to the CustInfo record sheet. After much
discussion, a solution was achieved.

But now I need to keep the original (000xxx00) format and only view
the custom format in both a cell and a UserForm TextBox.

For the sheet cell format, I tried these custom formats in the format
number menu:

"Map" ???X "<" XX-??""
"Map" #### "<" ##-##""
"Map "&####&" <"&##&"-"&##&""
"Map " & #### & " <" & ## & "-" & ## & ""
"Map "####" <"##"-"##""
"Map" #### <##-##
"!Map "0000 "\<"00-00"\"
"!Map @@@@ \<@@-@@\"

None of these work.

One other thing, the cell gets it's value from either this Offset
formula:

=IF(OFFSET(clStart,pfDisc,23)=0,"",OFFSET(clStart, pfDisc,23))

clStart is the reference row (A1 on CustList).
pfDisc is the row in the CustList sheet to be looked at.
23 is the column on that row where to data to be displayed is sitting.
Or direct entry in the edit mode which happens when this cell is
copied and pasted over itself with EditPaste specialValues, edited
copied back over to the CustList sheet as a new record and then the
formula is pasted back into the cell, There is some protection turned
off, on, off and back on also in the mix, but that should not be a
factor (I hope).

Anyone have any ideas to how to see Map 123A <BC-45 in a cell with
the cell value of 123abc45?

Any help, suggestions or direction would be appreciated.

-Minitman

Hey Pete,

Thanks for the reply.

The cell in question is a named range called pfCell_24. The form on
this sheet is used for two things. Viewing and editing.

Your solution works on the viewing side. However, I am having trouble
getting it to work on the editing side (something to do with some
columns I deleted).

When in view mode, pfCell_24 is locked and protected with the Offset
formula in place. By moving pfCell_24 to a hidden place and placing
your formula into the hole created by the movement of pfCell_24, I get
what I am looking for.

The edit mode changes will have to wait until I get all of the columns
added, moved and the code stabilized.

Again thanks for your help.

-Minitman




On Mon, 14 Jul 2008 22:54:02 +0100, "Pete_UK"
wrote:

If you don't mind it being in another column, you could do this:

="Map "&UPPER(LEFT(A2,4))&" <"&UPPER(MID(A2,5,2))&"-"&RIGHT(A2,2)&""

assuming 123abc45 is in A2. You could hide column A if it is the result you
want to see.

Hope this helps.

Pete

"Minitman" wrote in message
.. .
Greetings,

A while back I was looking to change the cell value formatted as
"000xxx00" to be "Map 000X <XX-00" this finale formatted value to be
copied in this new format to the CustInfo record sheet. After much
discussion, a solution was achieved.

But now I need to keep the original (000xxx00) format and only view
the custom format in both a cell and a UserForm TextBox.

For the sheet cell format, I tried these custom formats in the format
number menu:

"Map" ???X "<" XX-??""
"Map" #### "<" ##-##""
"Map "&####&" <"&##&"-"&##&""
"Map " & #### & " <" & ## & "-" & ## & ""
"Map "####" <"##"-"##""
"Map" #### <##-##
"!Map "0000 "\<"00-00"\"
"!Map @@@@ \<@@-@@\"

None of these work.

One other thing, the cell gets it's value from either this Offset
formula:

=IF(OFFSET(clStart,pfDisc,23)=0,"",OFFSET(clStart, pfDisc,23))

clStart is the reference row (A1 on CustList).
pfDisc is the row in the CustList sheet to be looked at.
23 is the column on that row where to data to be displayed is sitting.
Or direct entry in the edit mode which happens when this cell is
copied and pasted over itself with EditPaste specialValues, edited
copied back over to the CustList sheet as a new record and then the
formula is pasted back into the cell, There is some protection turned
off, on, off and back on also in the mix, but that should not be a
factor (I hope).

Anyone have any ideas to how to see Map 123A <BC-45 in a cell with
the cell value of 123abc45?

Any help, suggestions or direction would be appreciated.

-Minitman

You're welcome - thanks for feeding back.

Pete

On Jul 15, 8:58*pm, Minitman wrote:
Hey Pete,

Thanks for the reply.

The cell in question is a named range called pfCell_24. *The form on
this sheet is used for two things. *Viewing and editing. *

Your solution works on the viewing side. *However, I am having trouble
getting it to work on the editing side (something to do with some
columns I deleted).

When in view mode, pfCell_24 is locked and protected with the Offset
formula in place. *By moving pfCell_24 to a hidden place and placing
your formula into the hole created by the movement of pfCell_24, I get
what I am looking for. *

The edit mode changes will have to wait until I get all of the columns
added, moved and the code stabilized.

Again thanks for your help.

-Minitman