Check out some similar questions!

tpipkin · Mar 7, 2011, 06:21 PM

I need to delete the first (-) for example 101-1-7 need to change to 1011-7

ScottGem · Mar 7, 2011, 07:40 PM

Is the pattern ALWAYS xxx-x-x

tpipkin · Mar 7, 2011, 07:43 PM

Yes the pattern is always the same

ScottGem · Mar 7, 2011, 07:51 PM

=Left(A1,3) & Right(A1,3)

tpipkin · Mar 7, 2011, 07:52 PM

Yes the pattern is always the same

tpipkin · Mar 7, 2011, 08:01 PM

Scott Do I copy and paste this somewhere inside of excel?

ScottGem · Mar 8, 2011, 04:41 AM

Ok, I'll assume you have a column of cells with the data you want to change. Otherwise you would just click in the cell and edit that one cell.

So what you do is enter the formula in a cell in an empty column, usually to the right of the column you want to edit. You change the cell reference (A1) in what I gave you to the correct cell for your sheet. Next you copy that formula down the column as far as you need to. The final step is to highlight the column with the formula and select Copy, then Paste Special and paste the values over the original column. Do this in a copy in case something goes wrong.

tpipkin · Mar 8, 2011, 06:22 AM

Scott;
Sorry to sound slow, but I am still not getting it I have attached a sample of the file I need to change if you can send me a screen shot maybe that will help,

ScottGem · Mar 8, 2011, 09:12 AM

Nothing attached.

But I've attached a sample for you. In Column A are examples of your pattern. In Column B are the formulas to remove the first -. In Column C I used Paste Special to paste the results of the formula in Col B.

tpipkin · Mar 8, 2011, 09:25 AM

Scott do I place the formula in column B at the top as in your example? Or do I select column A and it will put the correct answer in B? Sorry for taking some much of your time

ScottGem · Mar 8, 2011, 01:09 PM

It depends on YOUR sheet. You generally put the formula in the cell to the right of where your text is, then copy it down the column.

tpipkin · Mar 8, 2011, 02:22 PM

I hope my attachment loads this time, I have copied and paste some actual data from my file, and some of them seem to be different I hope you can help the first formula works as long as they are not double digit.
102-4-25
102-4-26
102-4-27
102-4-28
102-4-29
102-4-30
102-4-32
102-4-33

Thanks Scott

ScottGem · Mar 8, 2011, 02:30 PM

See that's why I asked if the pattern was always the same.

Change the formula to:
Left(A1,3) & Right(A1,Len(A1)-4)

tpipkin · Mar 8, 2011, 02:36 PM

The first formula worked with the pipe, and not the comma should that be changed?
=LEFT(A3|3) & RIGHT(A3|3)
=Left(A1,3) & Right(A1,Len(A1)-4)

ScottGem · Mar 8, 2011, 02:45 PM

Hmm, worked for me with the comma. But if it works for you with the pipe instead then use that.