Google Spreadsheet how to remove unwanted space characters from cells containing email addresses

Question:

( by Boki7 )

Hi,

I'm manually importing databases of potential customers into spreadsheet and when I copy/paste email address there are empty fields in email addresses ( like: "some .address@ gmail. com" ). 

My question is how can I remove empty fields between or in these email addresses? I would like to use search function to delete all empty fields, but don't know how to do that (like: from "some .address@ gmail. com" to "some.address@gmail.com" )?

Kind regards ;)

Solution:

Have a look at the following screenshot:

You can also try the following formula in Cell E1:

=arrayformula(iferror(if(search("@";D:D);substitute(D:D;" ";""));D:D))

the above formula will look into the Column D and if the cell contains "@" then it will substitute the unwanted space from those cells only

I assume your other cells (not having email Ids) will not be having symbol "@"

and now you will have values in Column E,

Select whole column E and Copy (ctrl c) it.

And then select Column D and right click on Cell D1 and then select "Paste special" and "Paste values only"

after pasting values you can delete the column E.

Have a look at the animated screenshot of my spreadsheet:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive