removing a digit

[Richmandudeus]

Hi all, my name is Rich.

So in a nutshell, i need excel to report back what is in a cell without the digit imidiately after a decimal.
ex.
before after
7.0179 7.179
4.0170 4.170
2A.0158 2A.158

So a couple things to keep in mind...
1. i was losing last zero due to rounding and it needs to be there (underlined)
2.has to be a single cell formula

Hope someone could help me out! Thanks in advance.

 

[Hui]

Richmondudeus

Firstly, Welcome to the Chandoo.org Forums

If the decimal always has a 0 after it I'd use:
=SUBSTITUTE(A1,".0",".")

Otherwise:
=LEFT(A1,FIND(".",A1))&RIGHT(A1,LEN(A1)-FIND(".",A1)-1)

 

[Richmandudeus]

Excelent and thanks for the quick reply back.
Substitute works fine except i am still losing the 0 at the end of 4.170. Could that be avoided or will excel always round it off?

 

[Hui]

Set the cell to have 3 digits


But the cells with A in them are text and that won't work

 

[Somendra Misra]

Hi,

As per my understanding adding to @Hui formula try below formula:

=TEXT(SUBSTITUTE(H9,".0","."),"#.000")

Text in H9

Regards,

 

[NARAYANK991]

Hi Rich ,

If you have data of various decimal lengths , try this :

=TEXT(SUBSTITUTE(A1,".0","."),"0." & REPT("0",LEN(A1)-FIND(".",A1)-1))

Narayan