3

I imported a string of email addresses and each cell now ends with a comma. I'd like to remove the comma. I tried this formula:

=SUBSTITUTE(A1," ","")

But it didn't work. Is there another way to accomplish this? (addresses imported from a Word document) Screenshots: https://imgur.com/a/gBqbGgK

  • 2
    How did you import them? Usually the comma is used as a field separator and should not be imported. – Ron Rosenfeld Feb 17 at 13:54
  • 2
    When you indicate that "it didn't work", please describe or present screenshots of the result. It would also be useful if you provide links to the search queries and resources that you have used as a reference. – dmcgill50 Feb 17 at 14:24
  • 15
    What you tried seems to remove empty spaces, not commas... – bug-a-lot Feb 18 at 8:38
22

Substitute will work, you have forgotten to replace the comma.

Use:

=SUBSTITUTE(A1,",","")
|improve this answer|||||
  • I couldn't get the formula to work. What is the secret of using them? – user45848 Feb 17 at 14:39
  • 2
    try =SUBSTITUTE(A1;",";"") instead does this work ? – PeterH Feb 17 at 14:41
  • 5
    You have to look closely. This answer will work (for the cell A1, if you put this in cell B1, for example). Here, I've added some spaces: =SUBSTITUTE(A2 , "," , ""). The syntax is: =SUBSTITUTE( <cell to look at> , <text to replace, in quotes> , <text to replace it with, in quotes> ) In your question, you have for the <text to replace> a blank space. You wanted to put a comma there. – Tom Hundt Feb 17 at 23:52
  • 5
    Also: if you put the comma in the second parameter and it still doesn't work, then go to one of your data cells and select and copy the comma from there, and paste it into the formula's parameter -- on the very off chance that you're dealing with some weird Unicode character that looks like a comma, but isn't. But your first problem is definitely that you're searching for a space and not a comma. – Tom Hundt Feb 17 at 23:57
  • Fun fact would happen to have a string with multiple commas, you can use the option in SUBSTITUTE to let Excel know which instance you would want to replace, for example: =SUBSTITUTE(A1,",","",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))) to replace the very last instance. – JvdV Feb 18 at 9:59
15

You can use the Find & Replace tool to achieve this.

To access this, press CTRL + F, then click on the Replace tab. In the "Find" box, type , but keep the "Replace" box blank. Then click "Replace All".

enter image description here

If you only want to do the search on a few cells, highlight those cells before you open the Find and Replace window.

|improve this answer|||||
  • 2
    Be warned, if you have commas you want elsewhere in the spreadsheet "Replace All" will get rid of them too. In that case you will need to do Find Next and then click Replace only if you want to remove that particular comma – Dragonel Feb 17 at 22:20
  • OP understands that =FORMULA displays its value in the new cell where the formula lives. See linked screen shots, which show his attempts in column B. – Tom Hundt Feb 17 at 23:56
  • 2
    Or you could add, select a range then use replace all, to only replace commas in the range – PeterH Feb 18 at 7:56
  • 1
    @Dragonel It is much easier to mark the column you want the comm removed from. – Stian Yttervik Feb 18 at 7:58
6

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 example@nonexistant.com, Typing =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.--> example@nonexistant.com, 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.

  1. Type =32767+512/1000 into cell A5, press ENTER
  2. Move the cursor back on top of the number.
  3. Hold CTRL hit 1, a Dialog will appear.
  4. Click 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.
  5. Click Number and the value will re-appear.
  6. 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 C:\stripit.py

#!/usr/bin/env python3

import sys

line=sys.stdin.readline()

while line!="":
  print(line.strip('\n,'))
  line=sys.stdin.readline()

After installing Python3, Run it in cmd.com by typing:

C:\...> cd /d C:\
C:\> python3 ./stripit.py <inputfile >outputfile

... hit ENTER

|improve this answer|||||
  • 3
    The screenshots in the already demonstrate that the OP is comfortable using the formula in a separate cell. The Python solution is is odd because it requires the user to install additional software, and will also not function on a standard Word (.docx) or Excel (.xlsx) file, which are zip archives containing XML. They would need to save the addresses into a plaintext file, then run the script, then bring them back into Excel. This seems more easily accomplished directly within the application, or by using a library such as openpyxl. – Skoddie Feb 18 at 17:28
  • You might wish to ponder a bit on what "I imported a string of email addresses ..." really means. In my eyes this means that there is a source file which does not reside in Excel, YET... Most likely a text file (.TXT, .CSV, ...). On top of that; installing and USING Python is one of the best ways to get to know programming, generally, not related to Excel though - but VERY much usable even in conjunction with use of Excel. – Hannu Feb 19 at 16:57
4

Alternatively:

Select column A:A > Use: Text-To-Columns function > Check: Delimited > Check: Comma > Keep destination as $A$1, but make sure to select second column and check "Do not import column(skip)" > Click: Finish

|improve this answer|||||
1

Another solution which may be helpful for this sort of thing is to copy/paste the contents into a standard text editor (not an actual word processor, those tend to add a whole bunch of unnecessary formatting and stuff. A straight-up text editor like e.g. Notepad++, EditPad, etc. is what you want) and do the character replacements there, then copy/paste back into Excel. It's probably not necessary for something as simple as comma replacement, but I've frequently done this for more complicated text-replacement operations, e.g. things involving RegEx, block selection, etc. I pretty much always have a text editor and Excel open at the same time, and use them both together for things like this.

|improve this answer|||||
1
=Left (A1, length(A1)-1)

You could even add if(right (A1,1)=",") if it's intermittent.

|improve this answer|||||
  • 1
    Could you expand a little on what this formula is trying to do? – Burgi Feb 19 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.