Remove period "." from cells

[YasserKhalil]

Hello everyone

I have numbers like that : 120.1720
I need simply to remove the dot (Period)
I used this formula

Please Login or Register  to view this content.

I got this result 120172
I need to get this result 1201720

Thanks advanced

[JieJenn]

Well, if the cell is numeric value, Excel automatically remove the last zero. Give this UDF a try

Please Login or Register  to view this content.

[FDibbins]

hmm if you have 120.1720 in a cell, and this is the exact content In that cell, then it is text, not numeric. If the actual content is 120.172, and shows as 120.1720, then is just formatted to show 4 decimal places, and the last 0 is actually not there.

Try this (it will work on text or numeric)...
=--SUBSTITUTE(TEXT(A1,"#,##0.0000"),".","")

If you dont need it to be number, dont use the leading --

[jindon]

Possibly last 0 might be added by number format...

Please Login or Register  to view this content.

=RemovePeriods(A1)+0

[FDibbins]

JeiJenn...thanks for the rep

[YasserKhalil]

Thanks a lot for help
I tried both solutions on different patterns .. and i didn't get my desired results
As for UDF function : 34.10 >> 341 and i need it to be 3410
As for the formula : 34.10 >> 341000 and I need it to be 3410 ...

I tried to add this

Please Login or Register  to view this content.

But I didn't get my desired results as well

[YasserKhalil]

Mr. Jindon
i didn't notice your reply
That's exactly what I need.. Thank you very much

Thanks alot everybody for offering help

Just one point :
If I change any of the entries I have to press Ctrl + Alt +F9 to update results .. Can it be done automatically?

[JieJenn]

What is your cell formatting looks like?

[jindon]

Try add one line

Please Login or Register  to view this content.

[jindon]

Just one point :
If I change any of the entries I have to press Ctrl + Alt +F9 to update results .. Can it be done automatically?

If you add foliowng code toe the sheet module, it would be done when selection change.

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very much Mr. Jindon for all this great help
Regards

[FDibbins]

This is where it helps if you provide a range of examples of what you are working with, This should work if your numbers are text numbers...
=--SUBSTITUTE(TEXT(A1,"#,##0."&REPT(0,LEN(MID(A1,FIND(".",A1,1),99))-1)),".","")

[YasserKhalil]

Thanks Mr. FDibbins
It seems that formula didn't give me the required results ..The results are without zeros 34.100 should be 34100
But whn applying the formula I got 341

[FDibbins]

Thanks Mr. FDibbins
It seems that formula didn't give me the required results ..The results are without zeros 34.100 should be 34100
But whn applying the formula I got 341

Thats probably because the cell does not contain 34.100, it only contains 34.1 - it is formatted to show 34.100. my formula is count ACTUAL characters after the decimal point, not FORMATED decimals. Just because you see 34.100, doesnt mean thats what the cell contains

[YasserKhalil]

You're right Mr. FDibbins
i'm dealing with the formatted decimals not the actual characters
Thanks a lot for your great help

[FDibbins]

you are welcome, and thanks for the feedback