Monday, July 15, 2013

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:
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,

No comments:

Post a Comment