View original (French)

Remove points from numbers in Excel

7Xman Posted messages 69 Status Membre -  
 AM -
Hello,

I am looking to solve a little problem I have with an Excel document. It contains a column of numbers, SIRET numbers to be more precise. And these numbers have dots to separate the different parts. We would like to remove these dots automatically to avoid having to go through five thousand numbers.

Thank you in advance!
Configuration: Windows XP Internet Explorer 7.0

4 réponses

Réponse 1 / 4
dobo69 Posted messages 1593 Registration date   Status Membre Last intervention   835
 
Hello,
If it's to be done once:
Edit -> Replace and fill:
Find: .
Replace: whatever you want.

Otherwise, you can use a function:
=SUBSTITUTE(A1,"."," ") (replaces all . found in the text of cell A1 with spaces)
--
If you need anything, just ask me.
21
alex54
 
Thank you for this tip and the time saving! With the "SUBSTITUTE" formula, I couldn't get it to work; it gives me 0, but the first method.. perfect!
0
Anonymous user > alex54
 
Hello alex54,

I'm really glad you succeeded with the first method.
The second method indicated by dobo69 is accurate and works well.

If you didn't succeed with this second method, it seems to me that
it must be because of an extra letter T that you might have added:

It's not =SUBSTITUTE(...) but =SUBSTITUE(...)
This is for the French version of Excel, of course!

You can try with this new information, which will help you
later for another exercise if needed.

Best regards. 😊
0
AM
 
Magnificent ... it works :-)
In 2 seconds, 871821528.002.6 becomes 8718215280026
0