Excel 2007 : Removing characters in a cell

**whofan** · 09-22-2011, 04:43 PM

I have a list of contacts that has been corrupted during a sync between Outlook and Google Contacts There are 900 + email address that are affected. The email fields should read 'email@email.com' but now all read 'email@email.com SMTP email@email.com'.

I am new to anything but the most basic add/subtract formulas and have spent a few hours trying to search for a solution to this with no luck.

I need to remove everything after the end of (right of) the email addresses on the left including the space before 'SMTP'.

Is there a formula for trimming from (and including) the first space onward to the right? I tried using the RIGHT and LEFT formula functions and putting in a 'space' for the text and 40 for the characters but it just deletes all the text,

**shg** · 09-22-2011, 04:45 PM

How about ...

=LEFT(A1, FIND("SMTP", A1) -2)

**scottylad2** · 09-22-2011, 05:20 PM

Slight variation
=TRIM(MID(A1,1,FIND(" ",A1)))

**shg** · 09-22-2011, 05:33 PM

Hi, Scotty,

That's better, if it works. My thought, from the OP's comment, was that what appears as a space might instead be a non-breaking space.

**whofan** · 09-22-2011, 06:19 PM

Thank you for your suggestion. I’m sorry to ask what may be a dumb question but where should I be entering that formula?

I changed your formula to match my range of cell =LEFT(N1:N990, FIND("SMTP", N1:N990) -2)
I also tried =LEFT(N1, FIND("SMTP", N990) -2)

When I entered your formula in an adjacent cell and covered only the one cell it did work for that one cell =LEFT(N1, FIND("SMTP", N1 -2) Trying to use the ‘drag the corner’ method of copying that function and the copy/paste special/formula only copied the result of the original cell.

How can I get it to cover an entire range and where should I be entering it? I am assuming a blank cell off to the side or top…

**whofan** · 09-22-2011, 06:22 PM

Note than not every cell in the column contains an email address or any data...

**whofan** · 09-22-2011, 06:25 PM

When I apply this trim formula to one cell I get 'email@email.com SMTP"

**abousetta** · 09-22-2011, 06:27 PM

Hi,

Could you upload a sample wokrbook so we can test the solutions? You can dummy the actual emails.

abousetta

**whofan** · 09-22-2011, 07:26 PM

Won't let me attach a .csv. I am wondering if the .csv format could be part of the issue?

**Marcol** · 09-22-2011, 07:33 PM

Zip it and post the Zip.

**tkowal** · 09-23-2011, 11:23 AM

Assuming your Email Address are in Column A then why not use

=SUBSTITUTE(A1," SMPT","")

Then copy down

**scottylad2** · 09-23-2011, 11:39 AM

In adding this formula, I made the assumption that all email addresses had imported with the same additional Space and Text If your layoit is different then use Shg'sformula and adjust accordingly to refer to the cells where you hold the email addresses

=TRIM(MID(A1,1,FIND(" ",A1))) This formula will be entered in B1 if your email address with the additional space and SMTP are in A1. You then drag it down the B column adjacent to the rest of your list. Where you have the formula against a cell with no corresponding email address, you will get a #VALUE Error, Using Trim on it's own won't remove anything, it's used within the Formula to remove any extra spaces before or after the selection. Yo get rid of the #VALUE error we can add an error handler to the Formula using =IFERROR(TRIM(MID(A1,1,FIND(" ",A1))),"") which will leave you with a blank cell when the cell in column A is empty

If you're still not sure, upload an excel spreadsheet with a copy of your spreadsheet

**whofan** · 09-23-2011, 11:41 AM

Good advice, done!

Originally Posted by Marcol

Zip it and post the Zip.

**whofan** · 09-23-2011, 11:59 AM

OK, I tried this one. My email address column is N.
I entered =IFERROR(TRIM(MID(N174,1,FIND(" ",N174))),"") in the M column on line 174. That changed the N174 data correctly. When I drag/copy to the M cells below it repeats the email address that was listed in N174 in every cell.

**whofan** · 09-23-2011, 12:02 PM

Thanks for the suggestion but this one had no effect, The email address remained the same with the "email@email.com SMTP email@email.com" format

Originally Posted by tkowal

Assuming your Email Address are in Column A then why not use

=SUBSTITUTE(A1," SMPT","")

Then copy down

**whofan** · 09-23-2011, 12:05 PM

Saving as an .xls and applying the function formulas was no different so it is not a .csv issue

Originally Posted by whofan

Won't let me attach a .csv. I am wondering if the .csv format could be part of the issue?

**scottylad2** · 09-23-2011, 12:12 PM

ok, from the top line in the sample, from this dwpatton@dwp.com SMTP dwpatton@dwp.com

to be shortened to this? dwpatton@dwp.com

**whofan** · 09-23-2011, 12:18 PM

Yes. That is exactly it. Keeping in mind that, like my sample, there are two columns of email addresses and, some cells have no data. If that is a problem I can alsways sort the cells and apply the fix to just the cells with data.

Originally Posted by scottylad2

ok, from the top line in the sample, from this dwpatton@dwp.com SMTP dwpatton@dwp.com

to be shortened to this? dwpatton@dwp.com

**scottylad2** · 09-23-2011, 12:22 PM

Try these, works ok in Excel 2010

**scottylad2** · 09-23-2011, 12:26 PM

The CSV didn' t upload, saved as xlsx and you can save it on your side as csv if you require

**whofan** · 09-23-2011, 01:01 PM

First let me say I very much appreciate your help!

I am still doing something wrong. When I use the fill handle to drag and copy the formula down it only repeats the same email address result as the original cell I am dragging. What do I need to do differently to copy the formula but have it update to the new adjacent cell numbers?

Originally Posted by scottylad2

The CSV didn' t upload, saved as xlsx and you can save it on your side as csv if you require

**NBVC** · 09-23-2011, 01:05 PM

Go to Formulas tab, then Calculation Options... make sure Automatic is selected.

**whofan** · 09-23-2011, 01:15 PM

Thank you!! So simple if you just know where to look....

SOLVED!!

Originally Posted by NBVC

Go to Formulas tab, then Calculation Options... make sure Automatic is selected.

**whofan** · 09-23-2011, 01:25 PM

Thank you again scottylad2 for providing the magic formulas that worked!!

Originally Posted by scottylad2

The CSV didn' t upload, saved as xlsx and you can save it on your side as csv if you require

**scottylad2** · 09-23-2011, 01:30 PM

Glad to help

Excel 2007 : Removing characters in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Removing characters in a cell

Re: Help for non VBA user on removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Re: Removing characters in a cell

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions