How to format a cell with dashes and have the dashes appear in the FX?

[pixie222]

I have a list of account numbers that need to be listed as 000-0000-0000. I can format them using a custom format and have the dashes appear in the cell of the work sheet, but in the fx part, the account number is listed as 00000000000. In order for my macro to run correctly in our data base, we need the dashes to appear in the account number in the fx spot.

 

[Boller]

Do you want to do with VBA or a worksheet function in another column?

What does your macro do with the account numbers? Post the code.

With a worksheet function :-

<TABLE style="WIDTH: 246pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=328 border=0 x:str><COLGROUP><COL style="WIDTH: 246pt; mso-width-source: userset; mso-width-alt: 11995" width=328><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 246pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=328 height=17 x:str="'=LEFT(A1,3) & "-" & MID(A1,4,4) & "-" & RIGHT(A1,4)">=LEFT(A1,3) & "-" & MID(A1,4,4) & "-" & RIGHT(A1,4)</TD></TR></TBODY></TABLE>