I am trying to format a decimal number that has leading zeros and four
decimal places and want to remove the decimal but keep the structure
of the number

ie change 00000100.9730 to 000001009730.

I have tried various methods including changing the number to text the
doing a find and replace the decimal and replacing it with "" to no
avail.

I end up loosing the zeros.

Any suggestions would be appreciated

Hi Mark:

Perhaps:

=SUBSTITUTE(TEXT(100.973,"00000000.0000"),".","")

will work for you.

Regards,

Vasant.

"Mark Williams" wrote in message
om...
I am trying to format a decimal number that has leading zeros and four
decimal places and want to remove the decimal but keep the structure
of the number

ie change 00000100.9730 to 000001009730.

I have tried various methods including changing the number to text the
doing a find and replace the decimal and replacing it with "" to no
avail.

I end up loosing the zeros.

Any suggestions would be appreciated

If you put a single quote ' in front of the value and then search for
the decimal and replace it with nothing it should work:

Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=
_
xlByColumns, MatchCase:=False

hth

Mike



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/