Removing decimal from a cells value

M

Mark Williams

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
 
V

Vasant Nanavati

Hi Mark:

Perhaps:

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

will work for you.

Regards,

Vasant.
 
R

rhody

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top