Formula secret #1:
Any formula will only SHOW the calculated value in the cell where the formula resides. It will NOT remove the comma in the source data CELL.
So if A1 contains
=SUBSTITUTE(A1," ","") into A1, will replace the previous text.
If you on the other hand type
=SUBSTITUTE(A1," ","") into any other cell, then that cell will show the result of the calculation --e.g.-->
firstname.lastname@example.org, but never change the content of A1.
To change the actual TEXT-content of a cell you have to run e.g. VBA macros, Python or any other scripting language that has the means to change Excel-files. OR run e.g. a simple Python script before importing the data into Excel¹.
Display secret #1
The ACTUAL value in a cell can be DISPLAYED in many formats.
=32767+512/1000 into cell A5, press ENTER
- Move the cursor back on top of the number.
- Hold CTRL hit 1, a Dialog will appear.
Date and a corresponding format line to the right of it - and you will see
1989-09-16 appear in the Dialog, in place of
32767.512 that was present there just before your click.
Number and the value will re-appear.
- If you click
Date again and then choose one of the Date formats that include time information; e.g.
12:17:16 or similar will appear to the right of the date (depending on which format you clicked).
In hope that this helps to get over the first "threshold".
¹) Save this from NOTEPAD (not word nor wordpad), into
After installing Python3, Run it in cmd.com by typing:
C:\...> cd /d C:\
C:\> python3 ./stripit.py <inputfile >outputfile
... hit ENTER