Delete extra characters within a cell

**catfish252** · 04-05-2007, 11:17 AM

I have a cell which is used to store a phone number the format normally is:

xxx-xxx-xxxx where x= any number. When I brought this database base over from Access approximately 1100 cells came over with a format of this:

xxx-xxxab-xxxx. Where ab equals 78 in 770 different cells and where ab = 70 in 330 cells. The 12,500 other cells came over normally and display correctly. I have tried using find/replace along with using wilcards. I can select the records perfectly, but how do I go about getting rid of those two extra characters in those cells? They are always in the same position in each of the 1100 cells.

**BigBas** · 04-05-2007, 11:41 AM

Does this work?

Assuming your information is in column A, use formula:

=LEFT(A1,7)&RIGHT(A1,5) and fill that formula down as far as necessary.

Let me know if that works.

**catfish252** · 04-05-2007, 01:50 PM

BigBas -- Thanks for the response, that works for the individual cell say for instance G1, however in G4 it repeats the phone number from G1 into the H4 cell, but it does the trick, however I'm hoping there is a way to do all cells at one time like using G~ for instance where the tilde says I don't care what the cell number is just perform the equation all the way down the column. Thanks for the help.

**BigBas** · 04-05-2007, 02:17 PM

You can enter that formula into G1, then fill the formula down as far as you would like. It will automatically accomodate the Cell for that row.

To fill, enter the formula into G1. Then, point ur mouse cursor to the lower right side of the cell, until the cursor turns into a shape like this: + Once it turns into a plus shape, click, and drag down as far as you would like.

Let me know if that works.